Page 1 of 1
query code help
Posted: Sat Oct 02, 2010 5:55 pm
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))
Re: query code help
Posted: Sat Oct 02, 2010 6:27 pm
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))
{
...
Re: query code help
Posted: Sat Oct 02, 2010 6:42 pm
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
Re: query code help
Posted: Sat Oct 02, 2010 7:22 pm
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.
Re: query code help
Posted: Sun Oct 03, 2010 3:04 am
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 />";
Re: query code help
Posted: Sun Oct 03, 2010 3:18 am
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
Re: query code help
Posted: Sun Oct 03, 2010 12:32 pm
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.
Re: query code help
Posted: Sun Oct 03, 2010 5:32 pm
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'];