MySQL Commands

[vcherubini]

Hello:

I have a question on how to retrieve data from a mysql dbase.

I can input data into a database, with the following code:

[tt]

$result = mysql("test", "INSERT INTO links (link1, link2, link3, link4)
VALUES ('$link1', '$link2', '$link3', '$link4')"
[/tt]

Where all of the variables would be the names of text fields from a previous page.

The problem I have is how do I retrieve the data from the same dbase?

I try something like this:

[tt]

$result = mysql_query("SELECT * FROM (links)"
if (!$result) {
print "failed to perform query\n";
print mysql_error();
} else {
print &quot;$result<br>\n&quot;;
}

[/tt]

All that I get is something that says &quot;Resource Id#3&quot;.

I want it to print the data within the field.

Any help is appreciated. Thank you.

-Vic



vic cherubini
malice365@hotmail.com

http://www.epicsoftware.com

====

Knows: Perl, HTML, JavScript, C/C++, PHP, Flash, Director

Wants to Know: Java, Cold Fusion, Tcl/TK

====

 

[Bromrrrrr]

Ehrm....no offence, but first of all I would suggest you do some reading!. This shouldn't be a problem, especially for someone who 'knows' php.

your $result gives back a resource id because it is in fact a resource id, read the php documentation on how to use it. Someone who 'knows' php should be able to figure it out.

 

[sophisticate]

Well.. any question deserves an answer, not a chiding. Here goes.

A result id is kind of like an object. It holds your result data privately and gives you access to a bunch of methods to retrieve the data. These methods can be read about at

http://www.php.net/mysql.

In the meanwhile, here are a couple to get you started:
mysql_num_rows($result) will return an integer containing the number of rows in your query.
mysql_fetch_array($result) returns an associative *and* numerically indexed array of the current result row (use a while{} loop to iterate through rows).

Good luck.

brendanc@icehouse.net

 

[vcherubini]

Bromrrrrr:

No offense, but if you are going to mock me on tek-tips, at least use correct English. Also, there is no point in leaving a post if you are just going to make fun of me. You can know a language and still have questions on how it works.

sophisticate:

Thank you for the help. I am at school now, so I can't work on it, but when I get home, I will. Thanks for the help. I have been reading the phpManual, but with no prevail, have not been able to understand that part of it.

Thank you for the help.

-Vic

vic cherubini
malice365@hotmail.com

http://www.epicsoftware.com

====

Knows: Perl, HTML, JavScript, C/C++, PHP, Flash, Director

Wants to Know: Java, Cold Fusion, Tcl/TK

====

 

[Inssider]

Well... I'll give you an example and hopefully you'll be able to take it from there...
Say you have a table that looks like this:
[tt]
mytable
*************************
* id | value *
* ---|------------------*
* 1 | &quot;first value&quot; *
* 2 | &quot;second value&quot; *
* 3 | &quot;third value&quot; *
* 4 | &quot;fourth value&quot; *
* 5 | &quot;fifth value&quot; *
*************************
[/tt]
(hope that came out right)
Here's how to do the MySQL stuff:

//////////

If you want to retrieve values from a database, you'd first use a query:
[tt]$getvalues=mysql($DBname,&quot;select * from mytable&quot;[/tt]
So now you have the query all set, stored in $getvalues.

Here's how you store a single value, from the column &quot;value&quot; in mytable, into a variable:
[tt]$rownumber=3;
$value=mysql_result($getvalues,$rownumber,&quot;value&quot;[/tt]
Remember that arrays, which is how the row numbers are called in MySQL, start with the number 0, so the first row, whose id value is 1, would actually be row 0, so in our example, even though the variable $rownumber was 3, the actual row called was row 4. So now $value contains the value &quot;fourth value&quot;. (By the way &quot;mysql_result&quot; is only one way to store information from a mysql database. There are other ways as sophisticate indicated, but this is the simplest to learn first.)

Then there's a very simple function that's very useful:
[tt]$numvalues=mysql_numrows($getvalues);[/tt]
Easy. Now $numvalues stores the number of rows in the result table as specified in our query above. This numerical value, unlike the array discussed in the preceding paragraph, is our way of counting: 1=1, 2=2, etc.

//////////

That's really all you need to get values from the database, so I'll just jumpstart you with a sample for outputting.
[tt]
<HTML>
<HEAD>
<TITLE>MySQL database data retrieval</TITLE>
</HEAD>
<BODY>
<?PHP
$getvalues=mysql($DBname,&quot;select * from mytable&quot;
$numvalues=mysql_numrows($getvalues);
for($rownumber=0;$rownumber<$numvalues;$rownumber++)
{
$value=mysql_result($getvalues,$rownumber,&quot;value&quot;
echo $value,&quot;<BR>&quot;;
}
?>
</BODY>
</HTML>
[/tt]

Keep at it!

 

[vcherubini]

Inssider:

One more question:

What would the code be that makes the table that you have above?

Would I make a field called id and a field called value. Then for each member of the fields, have id have members 1-5 each with values 1-5 and value with the respective values?

Any help on how to make a table like that is appreciated.
Thank you.

-Vic

vic cherubini
malice365@hotmail.com

http://www.epicsoftware.com

====

Knows: Perl, HTML, JavScript, C/C++, PHP, Flash, Director

Wants to Know: Java, Cold Fusion, Tcl/TK

====

 

[Inssider]

Well, the ID part wasn't really necessary in my example, but it's good practice because you may find you need it for larger tables with more complicated queries.

But here's how you would do it:

First you create the table. This is done through telnet or you can use phpMyAdmin or something. Here's how I would do it:
[tt]
CREATE TABLE mytable(
id int(10) DEFAULT '0' NOT NULL auto_increment,
value char(50) DEFAULT '' NOT NULL
);
[/tt]

Now the table is all set and ready to store information. You would need to populate it by using some insert statements. This can be done through telnet/phpMyAdmin using:
[tt]INSERT INTO mytable (value) values (&quot;first value&quot[/tt]
in which &quot;first value&quot; would be replaced by whatever value you want to enter for that row. And because of the &quot;auto_increment&quot; property I set for my id field, it will automatically create a value for itself, incrementing by one, for each row I enter a value for the field &quot;value&quot;.

OR, I could use PHP and do something like:
[tt]
<HTML>
<HEAD>
<TITLE>My Page</TITLE>
</HEAD>
<BODY>
<?PHP
$numsuccess=0;
for($i=0;$i<5;$i++)
{
if($i==0)
{
$insertvalue=&quot;first value&quot;;
echo &quot;<BR>Inserting <BR>&quot;,$insertvalue,&quot;</B> into the MySQL database...&quot;;
}
elseif($i==1)
{
$insertvalue=&quot;second value&quot;;
echo &quot;<BR>Inserting <BR>&quot;,$insertvalue,&quot;</B> into the MySQL database...&quot;;
}
elseif($i==2)
{
$insertvalue=&quot;third value&quot;;
echo &quot;<BR>Inserting <BR>&quot;,$insertvalue,&quot;</B> into the MySQL database...&quot;;
}
elseif($i==3)
{
$insertvalue=&quot;fourth value&quot;;
echo &quot;<BR>Inserting <BR>&quot;,$insertvalue,&quot;</B> into the MySQL database...&quot;;
}
elseif($i==4)
{
$insertvalue=&quot;fifth value&quot;;
echo &quot;<BR>Inserting <BR>&quot;,$insertvalue,&quot;</B> into the MySQL database...&quot;;
}
if(mysql($DBname,&quot;INSERT INTO mytable (value) values ('$inservalue')&quot)
{
$numsuccess++;
echo &quot;<BR>Success!!<BR>&quot;;
}
else
{
echo &quot;<BR>Failure!!<BR>&quot;;
}
}
echo &quot;<P>&quot;,$numsuccess,&quot; out of 5 values inserted successfully!!&quot;;
?>
</BODY>
</HTML>
[/tt]

Hopefully I didn't make too many errors while typing that up. Does it help?

 

[sophisticate]

Just throwing this in as an example of concise coding:

Another way to do the above would be to iterate through an array of values...

$link = mysql_connect('host','user','pass');
$myArray = array('first value',
'second value',
'third value'); /* etc... */
for($i = 0; $i < count($myArray); $i++) {
mysql_query(&quot;insert into myTable(id,val) values($i,'$myArray[$i]')&quot;,$link);
}

This way, if you want to add more values, you don't have to create extra elseif statements, just add the value to the array.

Hope this helps,

brendanc@icehouse.net

 

[Inssider]

Hehe vikter use sophisticate's way as an example of good coding. Mine was pretty sloppy.

 

[vcherubini]

sophisticate:

Thanks for that. I am making an interface via web browser to update a mysql dbase, so an array won't help much. I am getting the user to input the value they want into a text field. But thanks for that, I will keep it in mind when doing another PHP application.

Thanks for all the help.

-Vic

vic cherubini
malice365@hotmail.com

http://www.epicsoftware.com

====

Knows: Perl, HTML, JavScript, C/C++, PHP, Flash, Director

Wants to Know: Java, Cold Fusion, Tcl/TK

====