Showing data from mysql

Squishy

Hey, I have a PHP book, it has code on how to fetch data from 3 different tables. But all my data is in 1 table not 3 which parts do I don't need?(code from book shown below)

<?php
/ Connect to the MySQL database /
$db_connection = mysql_connect("localhost", "username", "password")
or die("Could not connect to DB");

/* Select the MasteringPHP database */
mysql_select_db ("MasteringPHP", $db_connection)
	or die("Could not find DB");

/* Store the query to be executed in a variable */
$pets_query = "SELECT P.PetName, 
					O.FirstName, 
					O.LastName, 
					PT.CommonName 
			   FROM Pet P 
			   INNER JOIN Owner O ON O.OwnerID = P.OwnerID 
			   INNER JOIN Pet_Type PT ON PT.PetTypeID = P.PetTypeID;";

 /* Send the query to MySQL for execution */
$query_result = mysql_query ($pets_query, $db_connection);

/* Begin table to format data */
print "<table border=\"1\">\n";
print "<tr><td align=\"center\" colspan=\"4\" bgcolor=\"#ffff99\">";
print "<strong>Pets and Owners</strong>";
print "</td></tr>";

/* Loop over query displaying all returned rows */
while ($row = mysql_fetch_object ($query_result)) 
{
	print "\t<tr>";
	print "\t\t<td>" . $row->PetName . "</td>";
	print "\t\t<td>" . $row->FirstName . "</td>";		
	print "\t\t<td>" . $row->LastName . "</td>";
	print "\t\t<td>" . $row->CommonName . "</td>";		
	print "\t</tr>";
}
print "</table>\n";

/* Close the connection with the database */

mysql_close($db_connection);
?>

kevinsequeira

change this

$pets_query = "SELECT P.PetName,
O.FirstName,
O.LastName,
PT.CommonName
FROM Pet P
INNER JOIN Owner O ON O.OwnerID = P.OwnerID
INNER JOIN Pet_Type PT ON PT.PetTypeID = P.PetTypeID;";

$pets_query = "SELECT * from tablename";

reg
kevin

Squishy

Do i need the O. and P. stuff?

kevinsequeira

O. , P. and PT. is used as aliases since u are referring to more than one table, ie referrring to table Pet P , Owner O, Pet_Type PT

since u have only one table u dont need to use it.

i suggest u read up on SQL syntax, will help u understand the query better

reg
kevin

Squishy

Hey, thx for you help but one last question. If i wanted to if else stuff would i use $row->PetName or just PetName.

kevinsequeira

$row->PetName

Squishy

heh, sorry 1 more, how would you filter out data?

chinni_77

Use LIMIT or ORDERBY

Squishy

Can I have an example of how you would use those?

chinni_77

When you want to display your records by some field name in your table, you can write the query as:

$query="SELECT book_title,book_author FROM tbl_books ORDER BY book_price";

or if you want to limit displaying no.of records from a table, you can write it in this way:

//This query displays the records starting from the id no.6 to 11.
$query="SELECT book_title,book_author FROM tbl_books ORDER BY book_price LIMIT 5,6";

Is it clear?

Squishy

Yes, but the LIMIT 5,6 part, is that saying that its goign to show 5 and 6 or its going to show anything with 5 in 6

chinni_77

This query displays the records starting from the id no.6 to 11.

say you have a table books with the following records:

id book_title

1.abc
2.def
3.ghi
4.jkl
5.mno
6.pqr
7.stu
8.vwx
9.yzü
10.äöy
11.james
12.kimmi

Now, when you run the query which I've given you, it displays the following records:
6.pqr
7.stu
8.vwx
9.yzü
10.äöy
11.james

Squishy

I understand but how would you filter somthing that had somthing in a specific field.
like 1. abc
2.bdef
3.abc
how would you make it show 1 and 3?

chinni_77

Well, in such a case use LIKE

SELECT * FROM books WHERE book_title LIKE '%ab%';

Squishy

Hey, I am getting parse error on row 93, which is
while ($GFrow = mysql_fetch_object ($GF_result))
what could be wrong?(this is only a part of the whole page)

<html>
<head>
<title>MySQL Database Script</title>
</head>

<body>

<?php
/ Connect to the MySQL database /
$db_connection = mysql_connect("localhost", "squishy_Squishy", "password")
or die("Could not connect to DB");

/* Select the MasteringPHP database */
mysql_select_db ("squishy_FZMembers", $db_connection)
	or die("Could not find DB");

/* Get GF info */
$GF_query = "SELECT Rank, 
					BFName, 
					Points,
					Email,
					Division,
					Commander
					from Recruits
					WHERE Division
					Like 'GF'";
/* Get AB info */
$AB_query = "SELECT Rank, 
					BFName, 
					Points,
					Email,
					Division,
					Commander
					from Recruits
					WHERE Division
					Like 'AB'";
/* Get AC info */
$AC_query = "SELECT Rank, 
					BFName, 
					Points,
					Email,
					Division,
					Commander
					from Recruits
					WHERE Division
					Like 'AC'";
/* Get RE info */
$RE_query = "SELECT Rank, 
					BFName, 
					DateJoined,
					Email,
					Division
					from Recruits
					WHERE Division
					Like 'RE'";

 /* GF ---- Send the query to MySQL for execution */
$GF_result = mysql_query ($GF_query, $db_connection);

 /* AB ---- Send the query to MySQL for execution */
$AB_result = mysql_query ($AB_query, $db_connection);

 /* AC ---- Send the query to MySQL for execution */
$AC_result = mysql_query ($AC_query, $db_connection);

 /* RE ---- Send the query to MySQL for execution */
$RE_result = mysql_query ($RE_query, $db_connection);

/* Begin GF table to format data */
print "<table border=\"1\">\n";
print "<tr><td align=\"center\" colspan=\"4\" bgcolor=\"#ffff99\">";
print "<strong>Pets and Owners</strong>";
print "</td></tr>";


  print "<TABLE cellSpacing=\"0\" cellPadding=\"0\" border=\"0\" width=\"100%\">\n";
    print "<TBODY>";
    print "<TR>";
    print "<TD><IMG src=\"www.teamfz.org/bf1942/images/topic/rosters.jpg\"></TD>";
    print "</TR>";
    print "<center>";

print "<table width=\"500\" border=\"1\" cellspacing=\"1\" cellpadding=\"1\" bgColor=\"444d2e\" borderColorLight=\"green\" borderColorDark=\"black\">";
print "<tr><td colspan=\"5\"><center>Ground Forces Division</center></td></tr>";
print "<tr>";
print "<td bgcolor=\"444d2e\"><center>Rank</center></td>";
print "<td bgcolor=\"444d2e\"><center>Name</center></td>";
print "<td bgcolor=\"444d2e\"><center>Points</center></td>";
print "<td bgcolor=\"444d2e\"><center>E-Mail</center></td>";
print "<td bgcolor=\"444d2e\"><center>Division</center></td>";
print "</tr>"

/* GF  Loop over query displaying all returned rows */
while ($GFrow = mysql_fetch_object ($GF_result))
{


	if($GFrow->Commander == 1)
		$rank = Com.;
	else if($GFrow->Commander == 2)
		$rank = Gen.;
	else if($GFrow->Points <= 4)
		$rank = Pvt.;
	else if($GFrow->Points >= 5 <= 9)
		&rank = PFC.;
	else if($GFrow->Points >= 10 <= 39)
		$rank = Spc.;

Norman_Graham

You missed a semi-colon on the previous line (92), i.e. print "<tr>";

9 times out of 10 a parse error means a missing something (semi-colon, bracket or brace in almost all cases) on the previous line - PHP is looking for a semi-colon on line 92, doesn't find it, which means that line 93 is incorrect.

HTH

Norm