Simple query!

John_wilson

Ok I apologise in advance, I know this is a ridiculous question.

I have a query

$result = mysql_query("select * from number where number='$num';") or die(mysql_error());

it only returns one row.

I have used the below

$row = mysql_fetch_array($result);
$row = mysql_fetch_row($result);
echo $row['Number'];
echo $row[1]

to try to get the data to display.

I can't do it! I can make a whole table of 1500 results appear but not one! what am I doing wrong?

The variables are empty ( or at least not printing anything out) no errors are seen.

Regards

John

bradgrafelman

I'm confused on what you're asking... are you trying to retrieve just one row?

John_wilson

Sorry,
the query should retrieve one row. ( it does in phpmyadmin)
but its the displaying of the single row that is causing me no end of grief.

Like I said I can get a whole list just not this single row??

Regards

John

NogDog

Both mysql_fetch_array() and mysql_fetch_row() advance the result row pointer. Therefore the second one you call is going to return false if there was only one result row for the query, since the first would have read the first row, then moved the pointer to the end of the result set.

John_wilson

I understand (sort of) what you mean there, how do I access the initial line?

bradgrafelman

Well for one, the code you posted above attempts to retrieve the row twice, and the second call will overwrite the value of the first call.

Try doing this to get an idea what $row contains:

$row = mysql_fetch_assoc($result); 
print_r($row);

EDIT: Wow... two posts before I finish my reply. I must be getting slow.

bradgrafelman

John_wilson;10952395 wrote:
how do I access the initial line?

Easily: stop calling two mysql_fetch_*() functions instead of just one! 🙂

EDIT: Also note in your MySQL query, you wrote the column name as 'number' yet in the PHP code you referenced $row['Number']. MySQL may not care, but PHP can definitely tell the difference between 'number' and 'Number', so you might want to double check which is correct.

NogDog

Just do either a mysql_fetch_assoc(), mysql_fetch_array(), or mysql_fetch_row(), but not two of them in a row overwriting the same $row variable. (I normally use mysql_fetch_assoc().)

$result = mysql_query("select * from number where number='$num';") or die(mysql_error()); 
if(mysql_num_rows($result)) // might as well make sure we got something
{
   $row = mysql_fetch_assoc($result);
   echo $row['Number'];
}
else
{
   echo "No rows returned";
}

If you really want to call the same result row with two different functions (maybe as a test?), then you need to "rewind" the result row pointer:

$row = mysql_fetch_assoc($result);
echo $row['Number'];
mysql_data_seek($result, 0); // rewind back to start
$row = mysql_fetch_row($result);
echo $row[1] 
mysql_data_seek($result, 0); // rewind again
$row = mysql_fetch_array($result); // returns both assoc and numeric keys
echo $row['Number'];
echo $row[1];

FrozNic

I found this in a book you might find pretty straight forward:

<html>
	<head>
          <title>SQL Intro Cont.</title>
     </head>
     <body>
		<?php
			// 1. Create a connection to a database
			$db = mysql_connect("server","user","pass");
			if(!$db) {
				die("<br>Database connection failed: " . mysql_error() . "<br>");
			}

		// 1. Select a database to use
		$db_select = mysql_select_db("db_name", $db);
		if(!$db_select) {
			die("<br>Database selection failed: " . mysql_error() . "<br>");
		}

		// 3. Perform a query on the selected database
		// result is an object: a collection of rows.
		$result = mysql_query("SELECT * FROM table_name", $db);
		if (!$result) {
			die("Database query failed: " . mysql_error());	
		}

		// Use returned data (if any)
		while ($row = mysql_fetch_array($result)) {
			echo $row["menu_name"]." ".$row["position"]."<br>";
			// This can be used as well
			// but using the column names is clearer
			// echo $row[1]." ".$row[2]."<br>";				
		}

		// Close our connection
		mysql_close($db);
	?>
 </body>
</html>

FrozNic

John_wilson;10952280 wrote:
echo $row['Number'];
echo $row[1]

As a side note, I noticed two things, someone mentioned one of them: 'Number' needs to be exactly how it is typed in the column name in your database. $row[1] should be $row[0] if you're only returning 1 value in your query. Otherwise it will grab the 2nd result if there's 2 or more. It does this because there is a -1 offset in result sets and arrays etc. Everything starts at 0 not 1. That could also be what's causing your headaches.

bradgrafelman

FrozNic wrote:
$row[1] should be $row[0] if you're only returning 1 value in your query.

Except he's not only returning 1 value in his query - he's returning the entire row. Thus, if the numerical indexes are to be used, then the index should be the column number (minus 1, since arrays begin at 0 as you pointed out). Thus, it could be $row[0], $row[1], or $row[28] - it all depends on the DB structure (which is a very good reason to never use the numerical indexes in the first place 🙂).

FrozNic

Touche lol.. Duno why that skipped my mind. I always use column names, what was I thinking :o