php mysql newbie question

Trying to write mysql query to document

[Birdman]

Hello all. I'm just learning php and mysql. My first little project is to create a dynamic nav menu so I can alter it sitewide easily in the future.

I've got my db set up and I'm putting this code in my html.

<?
$dbh=mysql_connect ("localhost", "username", "password") or die ('I cannot connect to the database.');
mysql_select_db ("my_db");

$query = "SELECT * FROM `links` LIMIT 0, 30";
mysql_query($query);

mysql_close();
?>

I'm not throwing errors, I just don't get any output. Do I need an echo() in there?

[shady]

To get you started, replace

mysql_query($query);

with

$categories = mysql_query($query);
while($category = mysql_fetch_array($categories)) {
echo 'Category=' . $category[<fieldname>];
}

(Replace <fieldname> with the name of a field in the table)

This will list the categories. You can then extend this example to build the hrefs etc..

[jatar_k]

you would need a few things.

The result of your query in itself is not enough. It will only return a resource identifier and you must pass it to another function to use the results.

I usually use mysql_fetch_array() [php.net] and then access the data from that array. To use your exact example.

$query = "SELECT * FROM `links` LIMIT 0, 30";
$q = mysql_query($query);
while ($row = mysql_fetch_array($q))
{
$val = $row["somecol"];
echo $val . "<BR>";
}
mysql_close();

This should give you a list of your 30 links each on a new line.

[jatar_k]

you're too quick for me shady. ;)

[dingman]

You need to do several more things to get output. First, the mysql_query() call returns a "MySQL result resource" that you need to capture, as it has all the results in it, eg

$result = mysql_query($query);

Once you've got that, you need to go through it for results. There are lots of ways to do this, but one way might be:

while ($link = mysql_fetch_array($result)
{
echo $link['column_name'];
}

As I find myself saying quite often lately, I've been using PostgreSQL much more than MySQL lately, so to the extent that they differ, I'm likely to have done it the Postgres way. That's the basic idea, though. The database interaction functions won't just dump stuff to your output by themselves. This is clearly the right thing, since sometimes you might want to munge the data before displaying it, or even store configuration information for a web application in the DB.

[shady]

Sorry about that - must be down to my broadband connection! I'm sure your way is probably better - I've only been hacking away at php for a couple of months (I have been programming for 17 years though, so I guess that may have given me a headstart!)

[Birdman]

Thank you very much! Works perfectly:) Looks like you'll be seeing me around here alot more;)