[RESOLVED] Having Trouble querying my database

[ginginca]

I'm new to this and having a bit of trouble with the syntax.

<?php	  // Query the Database
$query = mysql_query("SELECT * FROM table_links"); 

while($row = mysql_fetch_array($query)) { 

echo "manufacturer".
   $row["manufacturer"] . "<br>"; 
} ?>

At the moment I have two questions.

(1) there is an error caused by the line that begins with "while"

(2) I want to only list items with a specific value in a field called cat.

Can someone assist me with this please?

Many thanks!

Gin

[Installer]

$specific_value = 'specific value';
$query = "SELECT * FROM table_links WHERE cat = " . $specific_value; 
$result = mysql_query($query); 

while($row = mysql_fetch_array($result)) { 
    echo 'manufacturer' . $row['manufacturer'] . '<br>'; 
}

As for why you're getting an error, it would be easier to guess at it if you copy-pasted the error message here. It probably has to do with no results from the query. Do you connect to the db before this code runs?

[ginginca]

Here's the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/pssi-ftp/public_html/plumbing.php on line 165

Line 165 is the line that starts with while.

[ginginca]

Installer wrote:

$specific_value = 'specific value';
$query = "SELECT * FROM table_links WHERE cat = " . $specific_value; 
$result = mysql_query($query); 

while($row = mysql_fetch_array($result)) { 
    echo 'manufacturer' . $row['manufacturer'] . '<br>'; 
}

As for why you're getting an error, it would be easier to guess at it if you copy-pasted the error message here. It probably has to do with no results from the query. Do you connect to the db before this code runs?

It looks like I shouldn't have said "specific value".

I am looking to list all items where the value of "cat" is equal to "Plumbing"

[Installer]

It looks like I shouldn't have said "specific value".

I don't know why not.

I am looking to list all items where the value of "cat" is equal to "Plumbing"

Then just install "Plumbing":

$specific_value = 'Plumbing';

From the error message it looks like your query is returning no results. Is the name of the table ("table_links") correct, and does it contain data?

[ginginca]

OK I get it.

I'll try that. Many thanks.

[ginginca]

Here's my code now.

<?php	  // Query the Database
 $specific_value = 'Plumbing';
$query = "SELECT * FROM table_links WHERE cat = " . $specific_value;
$result = mysql_query($query);

while($row = mysql_fetch_array($result)) {
    echo 'manufacturer' . $row['manufacturer'] . '<br>';
}  ?>

I am still getting an error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/pssi-ftp/public_html/plumbing.php on line 167

Line 167 is the one starting with "while"

[mjax]

It looks like your query is giving an error;

change the mysql_query() line to:

$result = mysql_query($query) || die mysql_error();

Are you connecting to the database using mysql_connect()? Was the connection succesful?

[ginginca]

mjax wrote:

It looks like your query is giving an error;

change the mysql_query() line to:

$result = mysql_query($query) || die mysql_error();

Are you connecting to the database using mysql_connect()? Was the connection succesful?

Here is my code now:

	  // Query the Database
 $specific_value = 'Plumbing';
$query = "SELECT * FROM table_links WHERE cat = " . $specific_value;
 $result = mysql_query($query) || die mysql_error(); 

while($row = mysql_fetch_array($result)) {
    echo 'manufacturer' . $row['manufacturer'] . '<br>';
} 

And the error now says:

Parse error: parse error, unexpected T_STRING in /home/pssi-ftp/public_html/plumbing.php on line 165

I had thought I was correctly connecting to the database. Does this mean I am not?

[mjax]

This means that the PHP parser has encoutered an error processing your script. The script itself is never executed. Please inspect what is at (and arround) mentioned line 165 and fix the problem. Could be a missing ; ) "

ginginca wrote:

Parse error: parse error, unexpected T_STRING in /home/pssi-ftp/public_html/plumbing.php on line 165

[Installer]

The syntax error is caused by not enclosing the argument for "die" in parentheses.

The MySQL error is caused by the query not returning any results. If the query itself were invalid (syntax or otherwise) or the connection was not made you would be getting an error message sooner. Change the "$query =" line to:

$query = "SELECT * FROM table_links WHERE cat = '" . $specific_value . "'";

If it still doesn't work, then the query is not returning any results. You need to check your database to see that the data you think is there actually is.

Comment out the "while" block and add this code right after the query line:

if ($result) {
    echo mysql_num_rows($result);
} else {
    echo 'No results';
}

[mjax]

You are right that the PHP error is caused by the lack of parentheses using the die() statement. However, the topicstarter is experiencing an error on his mysql_fetch_array() statement:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/pssi-ftp/public_html/plumbing.php on line 167

This error never occurs when a query is not returning any results (as in 0 rows). Most of the time, this means that there's a SQL error in the query. These kind of errors can be easily detected by outputting the mysql_error() return value after executing the query. Hence the die() statement.

So the check on mysql_num_rows() will not help him in this case.

Installer wrote:

The syntax error is caused by not enclosing the argument for "die" in parentheses.

The MySQL error is caused by the query not returning any results. If the query itself were invalid (syntax or otherwise) or the connection was not made you would be getting an error message sooner. The query is not returning any results. You need to check your database to see that the data you think is there actually is.

Comment out the "while" block and add this code right after the query line:

if ($result) {
    echo mysql_num_rows($result);
} else {
    echo 'No results';
}

[Installer]

You are correct.

[ginginca]

I'm not sure where this leaves me then.

There is definately a list of records that have a value of "Plumbing" in the cat field.

I tried changing it to querying based on a different field with different data, and I get the exact same error.

Gin

[mjax]

What output did that mysql_error() give you? Are you 100% sure that your script succesfully connected to the database?

[ginginca]

The connection information resides on a seperate page, and there is a line which refers to that page. I am confident to say it is connecting.

The error I currently receive is:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/pssi-ftp/public_html/plumbing.php on line 168

plumbing.php is the name of the page I am loading.

line 168 is

while($row = mysql_fetch_array($result)) {

[mjax]

Still, I'm quite sure that there is an error in your SQL query. After re-reading this complete topic, I may have spotted the error.

Please modify your code to look something like this:

 $specific_value = 'Plumbing';
$query = "SELECT * FROM table_links WHERE cat = '" . mysql_real_escape_string($specific_value) . "'";
$result = mysql_query($query);

if(!$result) die("ERROR: " . mysql_error());

while($row = mysql_fetch_array($result)) {
    echo 'manufacturer' . $row['manufacturer'] . '<br>';
} 

As you can see, I have modified the original query so that now the queried value of cat is in quotes. Also the mysql_real_escape_string() call was added to make sure that any special characters are escaped.

If there is some kind of error in the SQL, your script should stop and show the error message.

Please, let me know if this works.

[ginginca]

Voila ... we're getting somewhere now. (Thank you!!!!!)

The error now states: ERROR: No Database Selected

This is useful information. I'll redo that part of the file again.

[ginginca]

I think I'm missing something here.

Whether I put this code in the php page, or whether I put it in a seperate file and point to it, I have the same issue. ERROR: No Database Selected

Is there supposed to be a third component?

<?php

FileName="Connection_php_mysql.htm"

Type="MYSQL"

HTTP="true"

$hostname_database = "localhost";
$database_database = "nameofdatabase";
$username_database = "nameofuser";
$password_database = "password";
$database = mysql_pconnect($hostname_database, $username_database, $password_database) or trigger_error(mysql_error(),E_USER_ERROR);
?>

[mjax]

I completely misread your post. Here's my revised answer:

As the error states, you have not selected a database yet. So after the mysql_connect(), you need te execute this:

mysql_select_db($database_database, $database)

The mysql_connect() and mysql_pconnect() function connect to a database server. Once you've connected to the server you have to selected which database on this server you want to use. There can be hunderds of databases on one server.