Get From mysql Database Table

[Dusaro]

Ok, I got someone to help me fix this but he had no idea what the error was...

 

I have 2 tables, one called points and the other called members.

 

In members i have got:

id

name

 

In points i have got:

id

memberid

promo

 

I have the following code:

<?php
$con = mysql_connect("localhost","slay2day_User","slay2day");
if (!$con)
{
	die('Could not connect: ' . mysql_error());
}
mysql_select_db("slay2day_database",$con);
$sqlquery="SELECT Sum(points.promo) AS score, members.name, members.id = points.memberid Order By members.name ASC";
$result=mysql_query($sqlquery,$con);
while ($row = mysql_fetch_array($result))
{

//get data
$id = $row['id'];
$name = $row['name'];
$score = $row['score'];

echo "<b>Name:</b> $name<br />";
echo "<b>Points: </b> $score<br />" ;
echo "<b>Rank: </b>";
if  ($name == 'Kcroto1'):
  echo 'The Awesome Leader';
else:
if ($points >= '50'):
  echo 'General';
elseif ($points >= '20'):
  echo 'Captain!';
elseif ($points >= '10'):
  echo 'lieutenant';
elseif ($points >= '5'):
  echo 'Sergeant';
elseif ($points >= '2'):
  echo 'Corporal';
else:
  echo 'Recruit';
endif;
endif;
echo '<br /><br />';

}

?>

 

I am getting the following error when i do the query in mysql:

#1109 - Unknown table 'points' in field list

 

And when i open the webpage i get the following error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/slay2day/public_html/points/members.php on line 18

 

Please Help me?

[Psycho]

You haven't identified the tables to pull records from , i.e. the FROM clause for the query.

 

SELECT Sum(points.promo) AS score, members.name
FROM members
JOIN points ON members.id = points.memberid
Order By members.name ASC

[Dusaro]

Thank You So Much It Worked!

[Dusaro]

oh yes i was also wondering if it would be possible to show the members even if they have not got any points?

[cunoodle2]

Post your updated code and we will go from there.

[Dusaro]

okay, here it is:

 

<?php
$con = mysql_connect("localhost","slay2day_User","slay2day");
if (!$con)
{
	die('Could not connect: ' . mysql_error());
}
mysql_select_db("slay2day_database",$con);
$sqlquery="SELECT Sum(points.promo) AS score, members.name FROM members JOIN points ON members.id = points.memberid Order By members.name ASC";
$result=mysql_query($sqlquery,$con);
while ($row = mysql_fetch_array($result))
{

//get data
$id = $row['id'];
$name = $row['name'];
$score = $row['score'];

echo "<b>Name:</b> $name<br />";
echo "<b>Points: </b> $score<br />" ;
echo "<b>Rank: </b>";
if  ($name == 'Kcroto1'):
  echo 'The Awesome Leader';
else:
if ($points >= '50'):
  echo 'General';
elseif ($points >= '20'):
  echo 'Captain!';
elseif ($points >= '10'):
  echo 'lieutenant';
elseif ($points >= '5'):
  echo 'Sergeant';
elseif ($points >= '2'):
  echo 'Corporal';
else:
  echo 'Recruit';
endif;
endif;
echo '<br /><br />';

}

?>

[Psycho]

Go and find a tutorial or two on how to use JOINs in queries. It may be a little daunting at first, but knowing how to really use JOINs is a very powerful skill.

 

Here is a revised query. Although, note that 'score' will be returned as a null value, so be sure to add some handling to convert to 0. Also, IMHO it is advantageous to put line breaks in the query for readability. It is difficult to look at a long query all on one line and "see" any errors. When you put line breaks to separate the logical parts it is much more intuitive.

 

$sqlquery="SELECT Sum(points.promo) AS score, members.name
           FROM members
           LEFT JOIN points ON members.id = points.memberid
           ORDER BY members.name ASC";

 

Note the "LEFT JOIN" which means include the records on the LEFT (i.e. the first table) even if there are no records to join on the RIGHT (i.e. the second table)

[Psycho]

After second thought, there is an easy way for the query to return 0 when sum() returns a null value. Also, I just realized, that those queries will not work because there is no GROUP BY clause! Don't know how "it worked" for you.

 

Here is an updated query that has the requisite GROUP BY and will return 0 for records that don't have any associated points records

$sqlquery="SELECT IFNULL(SUM(points.promo), 0) AS score, members.name
           FROM members
           LEFT JOIN points ON members.id = points.memberid
           GROUP BY members.id
           ORDER BY members.name ASC";

[Dusaro]

Thank you so much! it works!