query code help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
scoob8254
Forum Newbie
Posts: 4
Joined: Sat Oct 02, 2010 5:51 pm

query code help

Post by scoob8254 »

hi, im getting an error which i cant seem to resolve, im guessing its a problem with my mysql query, but i cand put my finger on it. would appreciate any help you can offer

the error is
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /home/scoobster/domains/***********/public_html/******/inc/members.php on line 32

the code is
//Max displayed per page
$per_page = 5;

//Get start variable
$start = $_GET['start'];

//Count Records
$record_count = mysql_num_rows(mysql_query("SELECT * FROM webs_squads_members"));

//Count max pages
$max_pages = $record_count / $per_page;

if (!start)
$start = 0;

//Display Data
$GET = mysql_query("SELECT webs_user.nickname, webs_user.email, webs_user.Steamid, webs_user.Balance FROM webs_squads_members INNER JOIN webs_user ON webs_squads_members.userID = webs_user.userID LIMIT $start, $per_page");
while ($row = mysql_fetch_assoc(!GET))
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: query code help

Post by califdon »

The result returned by mysql_query() is not a value, it is a "resource", that is, a set of records that you have specified. It's a good practice to ALWAYS name the variable $resource or $res, just to remind you of this so you won't make this mistake. Once you have a resource, you can either fetch rows of data, or use a function like mysql_num_rows() to get the number of records. Do this:

Code: Select all

$resource = mysql_num_rows(mysql_query("SELECT * FROM webs_squads_members"));
$record_count = mysql_num_rows($resource);
Then, your second query has a similar problem. Do this:

Code: Select all

$resource= mysql_query("SELECT webs_user.nickname, webs_user.email, webs_user.Steamid, webs_user.Balance FROM webs_squads_members INNER JOIN webs_user ON webs_squads_members.userID = webs_user.userID LIMIT $start, $per_page");
while ($row = mysql_fetch_assoc($resource))
{
...
scoob8254
Forum Newbie
Posts: 4
Joined: Sat Oct 02, 2010 5:51 pm

Re: query code help

Post by scoob8254 »

thanks, can understand why it makes sense to do that, thanks for your help.

the code still doesnt work tho, and still get the same error.

thanks again
scoobs
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: query code help

Post by califdon »

Did you edit your post?? If you didn't, I misread it the first time. In any case, the only way anyone can help you find your problem is to display your code (and please use the PHP Code button to add syntax tags around your code so it is readable!).

Also, let MySQL help you find errors by asking it to display the errors in mysql commands. For example,

Code: Select all

 $sql = "SELECT * FROM webs_squads_members";
$resource = mysql_query($sql) OR die(mysql_error());
What that other error is trying to tell you is that when your code tries to execute the mysql_fetch command, it is expecting a resource recordset, but it's getting a boolean--a FALSE value--which means that your query failed and returned FALSE. So you need to find out why your query failed.
scoob8254
Forum Newbie
Posts: 4
Joined: Sat Oct 02, 2010 5:51 pm

Re: query code help

Post by scoob8254 »

thnaks a lot, learnign a lot here...

my sql query works perfect if ran on the db in phpmyadmin, so unsure why the syntax could be wrong ?.

after editing my code to try and view the mysql error i now see this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 5' at line 1

Code: Select all

 
 
 //Max displayed per page
 $per_page = 5;
 
 //Get start variable
 $start = $_GET['start'];
  
  //Count Records
  $resource = mysql_query("SELECT * FROM webs_squads_members");
  $record_count = mysql_num_rows($resource);
  
  //Count max pages
  $max_pages = $record_count / $per_page;
  
  if (!start)
     $start = 0;
     
 //Display Data
$sql= "SELECT webs_user.nickname, webs_user.email, webs_user.Steamid, webs_user.Balance FROM webs_squads_members INNER JOIN webs_user ON webs_squads_members.userID = webs_user.userID LIMIT $start, $per_page";
$resource = mysql_query($sql) OR die(mysql_error());
while ($row = mysql_fetch_assoc($resource))
 {
    // get data
    $name = $row['nickname'];
    $email = $row['email'];
    $Steamid = $row['Steamid'];
    $Balance = $row['Balance'];
    
    echo $name."   ".$email."   ".$Steamid."   ".$Balance."   <br />";
scoob8254
Forum Newbie
Posts: 4
Joined: Sat Oct 02, 2010 5:51 pm

Re: query code help

Post by scoob8254 »

looking at it think something is wrong with the $start variable as if i replace $start with 0 it works fine

ooops, my bad, missed the $ off in the if (!$start)

thanks for your help
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: query code help

Post by califdon »

Sounds like you may have that under control. If you continue to have problems, post back here again, and be sure to include the exact wording of any error messages you may receive. That and the code that's involved is what we always need to give you assistance.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: query code help

Post by John Cartwright »

Code: Select all

//Count Records
  $resource = mysql_query("SELECT * FROM webs_squads_members");
  $record_count = mysql_num_rows($resource);
You do not want to query the entire table rowset just to get a count of it. You could simply ask the query to return the count for you, which would be greatly more efficient. You may not notice the innefficiency here, but as your table grows this will become significantly slower.

Code: Select all

$resource = mysql_query("SELECT COUNT(*) AS `count` FROM webs_squads_members");
$row = mysql_fetch_assoc($resource);
$record_count = $row['count'];
Post Reply