Welcome to WebmasterWorld Guest from 54.158.143.40

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

php mysql newbie question

Trying to write mysql query to document

   
9:31 pm on Oct 16, 2002 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?

9:49 pm on Oct 16, 2002 (gmt 0)

10+ Year Member



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..

9:54 pm on Oct 16, 2002 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.

9:55 pm on Oct 16, 2002 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



you're too quick for me shady. ;)
9:57 pm on Oct 16, 2002 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.

9:58 pm on Oct 16, 2002 (gmt 0)

10+ Year Member



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!)
10:14 pm on Oct 16, 2002 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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