Forum Moderators: coopster
--------------------
Imagine this simple database of car makes and models:
tblmake:
makeid
make
tblmodel:
modelid
model
makeid
--------------------
I want to display all of the car makes in alphabetical order, in two columns down a page. The As, Bs, Cs, etc., must be separate, and must have the appropriate letter as a heading. For example:
A
Astom Martin
Audi
B
Bentley
BMW
--------------------
Each car make must show the number of models it has, in brackets. For example:
A
Astom Martin (12)
Audi (15)
B
Bentley (10)
BMW (22)
--------------------
At the top of the page, there must be links to the As, Bs, Cs, etc., thus:
A ¦ B ¦ C
A
Astom Martin (12)
Audi (15)
B
Bentley (10)
BMW (22)
<?php
$conn = mysql_connect('localhost', 'heru_tehutimaat', 'atumra') or trigger_error("SQL", E_USER_ERROR);
mysql_select_db('heru_ctyi', $conn) or trigger_error("SQL", E_USER_ERROR);
$query = "SELECT make, url FROM tblmake ORDER BY make";
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
if(mysql_num_rows($result) >= 1) {
while($row = mysql_fetch_assoc($result)) {
echo "<a href=\"" . $row['url'] . "\">" . $row['make'] . "</a><br />";
} // end while
} // end if
else {
echo "None";
}
?>
However, this just gives a list of the makes. I can't separate the As from the Bs, etc., plus I can't count how many models each car make has from the other table, tblmodel. What's more, I can't separate the list into two columns.
How do I both SELECT the car makes and COUNT each make's models in one command?
SELECT `Country`.`Name` , COUNT( `City`.`ID` ) AS cntCity
FROM `Country` , `City`
WHERE `Country`.`Code` = `City`.`CountryCode`
GROUP BY `City`.`CountryCode`
ORDER BY `Country`.`Name` ASC
You should be able to adapt that to fit your table names and field names. After that, I recommend splitting the A's, B's, C's, etc. using PHP. As you loop through the rows just keep track of what letter you are on and, if it changes, then echo a header.
With your help and with help from others, I now have this:
----------------------
<?php
$conn = mysql_connect('localhost','deleted', 'deleted') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('databasename', $conn) or trigger_error("SQL", E_USER_ERROR);
//Query ALL the records ordered by make
$query = "SELECT tblmake.make, tblmake.url, COUNT(tblmodel.modelid) AS model_count
FROM tblmake, tblmodel
WHERE tblmake.makeid = tblmodel.makeid
ORDER BY tblmake.make ASC";
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
if(mysql_num_rows($result) >= 1)
{
//There were results, let's display them
$current_letter = false;
while($row = mysql_fetch_assoc($result))
{
//Detected a new letter. Insert header anchor
if($current_letter != substr($row['make'], 0, 1))
{
$current_letter != substr($row['make'], 0, 1)
echo "<br /><h1><a name=\"$current_letter\">$current_letter</a></h1><br />";
}
//Display the current make with count
echo "<a href=\"{$row['url']}\">{$row['make']} ({$row['model_count']})</a><br />";
} // end while
} // end if
else
{
echo "None";
}
?>
----------------------
Unfortunately, it doesn't work:
Parse error: syntax error, unexpected T_ECHO in [Location] on line 126
Can you see the problem?
if($current_letter != substr($row['make'], 0, 1))
{
[b]$current_letter != substr($row['make'], 0, 1) [/b]
echo "<br /><h1><a name=\"$current_letter\">$current_letter</a></h1><br />";
if($current_letter != substr($row['make'], 0, 1))
{
[b]$current_letter = substr($row['make'], 0, 1);[/b]
echo "<br /><h1><a name=\"$current_letter\">$current_letter</a></h1><br />";
The code was provided by a friend. I adapted it for my database but have failed to get it to work. Here's the error:
"Parse error: syntax error, unexpected T_ECHO in [Location] on line 126"
I tried to remove the part that you highlighted. I, too, thought it looked erronous. But when I removed it, I got a fatal error.
Why doesn't this code work?
----------------------
<?php
$conn = mysql_connect('localhost','deleted', 'deleted') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('databasename', $conn) or trigger_error("SQL", E_USER_ERROR);
//Query ALL the records ordered by make
$query = "SELECT tblmake.make, tblmake.url, COUNT(tblmodel.modelid) AS model_count
FROM tblmake, tblmodel
WHERE tblmake.makeid = tblmodel.makeid
ORDER BY tblmake.make ASC";
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
if(mysql_num_rows($result) >= 1)
{
//There were results, let's display them
$current_letter = false;
while($row = mysql_fetch_assoc($result))
{
//Detected a new letter. Insert header anchor
if($current_letter != substr($row['make'], 0, 1))
{
$current_letter = substr($row['make'], 0, 1);
echo "<br /><h1><a name=\"$current_letter\">$current_letter</a></h1><br />";
}
//Display the current make with count
echo "<a href=\"{$row['url']}\">{$row['make']} ({$row['model_count']})</a><br />";
} // end while
} // end if
else
{
echo "None";
}
?>
----------------------
However, the code still doesn't work as intended. It only echoes one heading letter - A - and one result beneath the heading. There are no Bs, Cs, etc. It looks like this:
A
Audi (90)
Note the count of 90. The number includes all of the models in the database, not just the models for Audi.
Anyone know why? Any help will be very much appreciated.
You're missing the GROUP BY in your SQL statement. In my example, the correct field to GROUP BY was City.CountryCode... I think in your case, you'd want tblmodel.makeid.
As you suggested, I added the Group By segment to the command and, after making some other changes, it worked!
---------------------
<?php
$conn = mysql_connect('localhost','hidden', 'hidden') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('hidden', $conn) or trigger_error("SQL", E_USER_ERROR);
//Query ALL the records ordered by make
$query = "SELECT tblmake.make, tblmake.url, COUNT(tblmodel.modelid) AS model_count
FROM tblmake
LEFT JOIN tblmodel ON tblmake.makid = tblmodel.makeid
GROUP BY tblmake.makid
ORDER BY tblmake.make ASC";
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
if(mysql_num_rows($result) >= 1)
{
//There were results, let's display them
$current_letter = false;
while($row = mysql_fetch_assoc($result))
{
//Detected a new letter. Insert header anchor
if($current_letter != substr($row['make'], 0, 1))
{
$current_letter = substr($row['make'], 0, 1);
echo "<br /><h1><a name=\"$current_letter\">$current_letter</a></h1>";
}
//Display the current make with count
echo "<a href=\"{$row['url']}\">{$row['make']}</a> ({$row['model_count']})<br />";
} // end while
} // end if
else
{
echo "None";
}
?>
---------------------
However, the code still doesn't do exactly what I want it to do.
Anyone know how I can incorporate the following condition in the COUNT?
(adddate >= CURDATE() OR adddate is NULL)
In other words, anyone know how I can get it to COUNT only the models whose adddates are NULL or greater than or equal to the current date?
When doing a select on multiple tables, you can add to the WHERE statement items that only affect one table, you just need to put the correct table name before each field name. So, you could add something like this:
AND (`tblmodel`.`adddate` >= CURDATE() OR `tblmodel`.`adddate` IS NULL)
I no longer need any help with this matter, guys.
