Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

how to get data from the mysql database

Quote Reply
how to get data from the mysql database
Hi there,

I hope that someone can help me starting to integrate my .cgi I used with links 2.0 for links sql. I need to access some data from the links database and had it stored in an array before. The code was like:

| | &ws_error('open->product_info', $PRODUCT_INFO);
while ($record= <PRODUCTS> ) {
local($ref_name, $name, $weight, $datum, $filename, $note, $price, $euro) = split(/\Q|\E/o, $record);
$PRODUCT_WEIGHT{$ref_name} = $weight;
$CONFIG{'PRODUCT_WEIGHT_' . $ref_name} = $weight;
$PRODUCT_NAME{$ref_name} = $name;
$CONFIG{'PRODUCT_NAME_' . $ref_name} = $name;
$PRODUCT_COST{$ref_name} = $price;
$CONFIG{'PRODUCT_PRICE_' . $ref_name} = $price;
$PRODUCT_NR{$ref_name} = $ref_name;
$CONFIG{'PRODUCT_NR_' . $ref_name} = $ref_name;

push(@products, $ref_name);

I know that I have to add:
use CGI ();
use CGI::Carp qw/fatalsToBrowser/;
use Links;
use Links: BSQL;
use Links: B_Utils;
use Links::HTML_Templates;
use strict;

but I don´t know how I can access the data then. Thanks for your help

Quote Reply
Re: how to get data from the mysql database In reply to
Take a look at jump.cgi.

Follow how it works.

Then, look at search.cgi, and follow how it works.

you MUST follow the logic of these two programs in order to understand how to get data out of the database. Once you think you have it figured out, and at least see what is going on, then you can start to change one of those two programs to meet your needs.

If all you want to do is take a known "ID" and find out all about it, use jump.cgi.

If you need to "look" for something first, then do something with it, use search.cgi.

If you need to add to the database, look at add.cgi, but jump.cgi also includes "update" lines, since it updates the hit_track files.

Then, once you understand how those two files work, if you still have the question, ask it again, something like ... I need to get a record from the Links database, where x=y and/or z=f, how do I do it?

The only real tricky part is knowing whether you are getting (or working with) the array or hash itself, or a reference to the array or hash. I find the reference to be much easier <G>.

Also, watch the %in hash. The CGI %in hash can't be changed, but usually somewhere in the program the CGI %in hash has been changed to a local %in hash, that _can_ be changed and modified and is _not_ the same as the CGI %in hash !!

I can't figure out what you are asking ... because I don't know what table it's in, and what you want to do with it, and how you are finding it...

With SQL, you PREPARE a query, then EXECUTE it. You then test the result and fetch_hash or fetch_array the values to work with. You can also execute a do-> command, that tells the database to just do what it's told NOW. This is great for inserting new data to the table (as in a new hit-track for jump.cgi) or an update of a known record's value such as the hit-count in the build_update in jump.cgi.

The hardest part about SQL programming, is forgetting everything you learned about flat file databases, and non-object oriented perl, and starting to think "objects" "methods" and "tables" rather than subroutines and files.

BTW... if you need to figure out how to push a bunch of stuff, look inside DBSQL.pm at the search routines, where it pushes the errors onto a stack to return. IT shows the differences in what you need to do with Links SQL from Links 2.0.

BTW... if you have specific questions about what is going on, I can try to answer, but really, this is a bit vague to try to do it with.

But, maybe this will help... when you do a fetch of a record from a query-> SELECT type statement, what is returned is a list of pointers. You iterate through the list using the fetch commands mentioned above (you need to get the MySQL or DBI book to really understand how that works, or read the on-line docs).

If you know what record you want ie: the ID # use the code from jump.cgi. If the record exists, what is returned is the pointer to the ROW OF DATA in that table.

You can then access the individual elements of that data using the reference notation:


For example, cutting out parts of jump.cgi:

This is the "ID" passed in to the CGI.pm wrapper:

$id = $in->param('ID');

You access that "in" hash with the 'param' method.

Next you have to create a connection to the database.

$db = new Links: BSQL $LINKS{admin_root_path} . "/defs/Links.def";

You've told it which database, and what def file to use for the field verifiers.

Then, you want to get the record that matches the ID you were looking for:

$rec = $db->get_record ($id, 'HASH');

If you found it, $rec will now be a pointer to the record.

$rec or &error ("Can't find link id in my new routine: $id");

You access the data as:


Searching by ID is the most efficient, since it's an indexed field,
and Links has all sorts of mechanisms such as get_record set up to
use the ID field.

Quote Reply
Re: how to get data from the mysql database In reply to
Thank you very much pugdog. I think that helps me to go on. I am sorry for asking those questions but I really do have problems I mean I started working around with perl, felt like I could start to realize that maybe one day there would be light and I could see, played around with some cgi´s and then it was like falling back on the floor with sql. I think the jump.cgi should be just perfect, because I need it for a shop, look up the product with the ID and amount stored in a cookie. Once more thank you very much.