Jump to content

Get From mysql Database Table


Dusaro

Recommended Posts

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?

Link to comment
Share on other sites

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 />';

}

?>

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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";

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.