grr SQL and PHP

denby · 20 Dec 2011 at 09:15

Hi Guys

no matter what i try i can't get this right
i either get nothing or resource id 2

im trying to run this SQL Query

Code:

SELECT credits FROM members WHERE username='sean'

which should simply return a number 2

my code is

Code:

<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("members", $con);

$result = mysql_query("SELECT credits FROM members WHERE username='sean'
while($row = mysql_fetch_array($result))
  echo $results['credits'];



?>

what am i missing?

JHeaton · 20 Dec 2011 at 09:26

The query in $result looks incomplete. Shouldn't that end with ");? Also, $results doesn't seem to be set anywhere; should that not be $result? I've only played a little with PHP so I could be wrong, but those are the two things that stand out for me.

denby · 20 Dec 2011 at 09:35

changed to

Code:

<?php
$host="localhost"; // Host name
$username="sbsuppli_sean"; // Mysql username
$password="liverpool"; // Mysql password
$db_name="sbsuppli_test"; // Database name
$tbl_name="members"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");


$q = mysql_query("SELECT credits FROM $tbl_name WHERE username= 'sean'");
$r = mysql_query($q);
while ($part = mysql_fetch_assoc($r)) 

echo $part;

?>

still shows nothing

topdog@OC · 20 Dec 2011 at 12:24

You're taking the results of a query, as a recordset, and trying to run a second query against it (two calls to mysql_query() in succession) - isn't something that makes sense to me.

I typically do row iteration too, haven't got experience with mysql_fetch_assoc, and you'd use that like this:

Code:

// ...
                $q = @mysql_query("SELECT credits FROM $tbl_name WHERE username= 'sean'");
                while($r = mysql_fetch_array($q))
                {
                    echo $r['credits'];
                }
// ...

denby · 20 Dec 2011 at 12:30

topdog@OC said:
You're taking the results of a query, as a recordset, and trying to run a second query against it (two calls to mysql_query() in succession) - isn't something that makes sense to me.

I typically do row iteration too, haven't got experience with mysql_fetch_assoc, and you'd use that like this:

Code:

// ... $q = @mysql_query("SELECT credits FROM $tbl_name WHERE username= 'sean'"); while($r = mysql_fetch_array($q)) { echo $r['credits']; } // ...

perfect!

thanks mate