[SOLVED] Complicated Project. Need Advice on How to Start

[Fluoresce]

I want to do something which is way out of my newbie league. I am going to describe it and hope that one of you pros can give me some tips on how to start. Any help will be appreciated.

 

--------------------

 

Imagine this simple database of car makes and models:

 

make_table:

makeid

make

 

model_table:

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)

[RichardRotterdam]

thats not that complicated. First of you display has nothing to do with you database design. You separate the cars alphabetically using php. You use a query to fetch all cars or all cars starting with a letter.

 

your database table would be like

cars

car_id                (int primairy key)

brand_id            (int foreign key)

car_name          (varchar)

car_description  (text)

car_image_url    (varchar 255)

 

brands

brand_id

brand_name

 

if you want a car to have multiple images then have a separate table for that

[Fluoresce]

Thanks for the effort, DJ Kat. I know that stuff, dude.

[Gighalen]

when you execute the query to retrieve the data from the database you will have to use a ORDER BY clause at the end of the query. (SELECT * FROM cars ORDER BY car_name ASC) will select the cars in alphabetical order from the database and display them.

 

To show the number of cars in each category, simply use the mysql_num_rows function.

 

To select stuff by A, B, C etc, use the MySQL LIKE statement. LIKE "A", LIKE "B", etc.

 

Hope this points you in the right direction.

[Fluoresce]

Thanks to MjDamato and others, I 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 properly:

 

"Parse error: syntax error, unexpected T_ECHO in [Location] on line 126"

 

Can anyone see the problem?

[Psycho]

You apparently are only posting part of the code from your page, because that code only has 38 lines and the error is occuring on line 126. When you have a line number to work with it is much easier to find the problem. In this case though, I was able to spot the problem (as well as another one):

 

In this section, I 1) forgot to change the "!=" to just "=" in the line after the IF condition and 2) forgot to include a semi-colon at the end of the same line

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

 

NOTE: I only post my code as a guide on a methodology that should work. I typically do not test it - especially when it involves a database that I do not have access to and do not want to try and replicate. I figure syntax errors should be easily solved by the person receiving the code.

[Fluoresce]

Thanks for the response, MJ!

 

I made the changes and they stopped the error. The problem is, the code doesn't work as intended. It only shows one result for the letter A, and that's it. It looks something like this:

 

A

 

Audi (90)

 

Note the count of 90. It includes all of the models in the database, not just the models for Audi.

 

I will play with the script to see what I can do. If you - or anyone else - can think of anything, I will appreciate the help. Otherwise, thanks for your help so far.

[Psycho]

The query you posted above is not the one I posted for you in a different thread. There is no GROUP BY clause - which is probably the problem.

 

<?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
          LEFT JOIN models ON tblmake.makeid = tblmodel.makeid
          GROUP BY tblmodel.modelid
          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";
}

?>

[Fluoresce]

MJ, it worked! Thank you very much! I had to make a few tiny changes, but in the end, it worked:

 

<?php

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

?>

 

Can I ask you another quick question, please?

 

Is it possible to echoe the results in a table with two colums, with 13 letters on either side?

[Psycho]

Um, not sure what you are asking for is what you really want. There will be many letters without any results. Do you want letters without results listed (e.g. 'Z')? And how do you want the letters displayed?

 

Like this

A          B
C          D
E          f
...

 

Or like this

A          N
B          O
C          P
...

 

Edit: Also, post the code that you have working for any changes to be made.

[Fluoresce]

Um, not sure what you are asking for is what you really want. There will be many letters without any results. Do you want letters without results listed (e.g. 'Z')? And how do you want the letters displayed?

 

Like this

A          B
C          D
E          f
...

 

Or like this

A          N
B          O
C          P
...

 

Edit: Also, post the code that you have working for any changes to be made.

 

Yes, I would like all letters to display, even if there are no makes for a particular letter (otherwise, the letter links at the top of the page won't work). This will give me two neat columns with 13 letters each, like this:

 

A    N

B    O

 

Is that difficult?

 

Here's my code so far:

 

<table class="center" style="text-align:center" width="400px" border="0" cellpadding="5" cellspacing="5">
<tr>
<td>

<a href="#A">A</a>
<a href="#B">B</a>
<a href="#C">C</a>
<a href="#D">D</a>
<a href="#E">E</a>
<a href="#F">F</a>
<a href="#G">G</a>
<a href="#H">H</a>
<a href="#I">I</a>
<a href="#J">J</a>
<a href="#K">K</a>
<a href="#L">L</a>
<a href="#M">M</a>

</td>
</tr>
<tr>
<td>

<a href="#N">N</a>
<a href="#O">O</a>
<a href="#P">P</a>
<a href="#Q">Q</a>
<a href="#R">R</a>
<a href="#S">S</a>
<a href="#T">T</a>
<a href="#U">U</a>
<a href="#V">V</a>
<a href="#W">W</a>
<a href="#X">X</a>
<a href="#Y">Y</a>
<a href="#Z">Z</a>

</td>
</tr>
</table>




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

?>

 

I really do appreciate the help, MJ. I'm learning loads off you.

[Psycho]

Well, you could just as easily create the links at the top to only include the letters for which there are results!

 

What you are asking is easy enough, but one possible issue is that the lists on each side might not be uniform. For example, if there are a lot of records in the A-M range, the left side of the list could be much longer than the list on the right.

 

<?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);
$total_records = mysql_num_rows($result);

if($total_records > 0)
{
    //There were results, let's display them
    $letters = array ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');

    $row = mysql_fetch_assoc($result)

    foreach($letters as $letter)
    {
        $letter_idx = array_search($letter, $letters);
        $side = ($letter_idx < 13)?'left':'right';
        $td[$side] .= "<br /><h1><a name=\"$letter\">$letter</a></h1>\n";

        while (substr($row['make'], 0, 1) == $letter)
        {
            $td[$side] .= "<a href=\"{$row['url']}\">{$row['make']}</a> ({$row['model_count']})<br />\n";
            if (!$row = mysql_fetch_assoc($result)) { break; }
        }
    }
} // end if

else
{
    echo "No results";
}

?>

<table class="center" style="text-align:center" width="400px" border="1" cellpadding="5" cellspacing="5">
<tr>
  <td colspan="2">
    <a href="#A">A</a>
    <a href="#B">B</a>
    <a href="#C">C</a>
    <a href="#D">D</a>
    <a href="#E">E</a>
    <a href="#F">F</a>
    <a href="#G">G</a>
    <a href="#H">H</a>
    <a href="#I">I</a>
    <a href="#J">J</a>
    <a href="#K">K</a>
    <a href="#L">L</a>
    <a href="#M">M</a>
  </td>
</tr>
<tr>
  <td colspan="2">
    <a href="#N">N</a>
    <a href="#O">O</a>
    <a href="#P">P</a>
    <a href="#Q">Q</a>
    <a href="#R">R</a>
    <a href="#S">S</a>
    <a href="#T">T</a>
    <a href="#U">U</a>
    <a href="#V">V</a>
    <a href="#W">W</a>
    <a href="#X">X</a>
    <a href="#Y">Y</a>
    <a href="#Z">Z</a>
  </td>
</tr>
<tr>
  <td valign="top"><?php echo $td['left']; ?></td>
  <td valign="top"><?php echo $td['right']; ?></td>
</tr>
</table>

[Fluoresce]

MJ, thank you for yet another great response!

 

Your new code looks great, but when I tried it, I got an error:

 

"Parse error: syntax error, unexpected T_FOREACH in [Location] on line 123"

 

I looked through the code to try to identify the problem, but then I remembered that I can't read PHP properly yet! Can you - or anyone else - see the problem?

 

Whatever the case, I hereby dub MJ my PHP Grand Master!

[samshel]

there is syntax error 1 row above, semicolon is missing....please read the error carefully ! Read Mj's signature

[Fluoresce]

there is syntax error 1 row above, semicolon is missing....please read the error carefully ! Read Mj's signature

 

Thanks, Samshell!

 

Grand Master MJ, it works!

 

Thank you very much!

 

Let me play with it a bit - I'm talking about the code - to see if everything's cool.

[Psycho]

....please read the error carefully ! Read Mj's signature

 

Wow, someone actual read that! I will try to test code if it doesn't mean I have to put a lot of work into it. If a database is involved I usually won't.

[Fluoresce]

I've been playing with that code, MJ. Great work! It's almost perfect. There are two minor problems:

 

1) When there are no car makes for a letter, the letter appears but not the word 'None':

 

Q

 

R

 

Renault (7)

Rolls Royce (21)

 

2) As you prediced, one column is much longer than the other. The only way this can be fixed is if A and N, B and O, etc., are put into table rows, thus:

 

A N

---

B O

---

C P

 

Would that require a major change of the code?

[Psycho]

1) When there are no car makes for a letter, the letter appears but not the word 'None'

 

And, where did you ask for that? I do like helping people, but there's nothing more frustrating than taking the time to help someone only to have them ask for something different once you provide what they originally asked for.

 

The code below should get you what you want (not tested).

 

<?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);
$total_records = mysql_num_rows($result);

if($total_records > 0)
{
    //There were results, let's display them
    $letters = array ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');

    $row = mysql_fetch_assoc($result);

    foreach($letters as $idx => $letter)
    {
        $no_makes_in_letter = true;

        $td[$idx] .= "<td><h1><a name=\"$letter\">$letter</a></h1><br />\n";

        while (substr($row['make'], 0, 1) == $letter)
        {
            $no_makes_in_letter = false;
            $td[$idx] .= "<a href=\"{$row['url']}\">{$row['make']}</a> ({$row['model_count']})<br />\n";
            if (!$row = mysql_fetch_assoc($result)) { break; }
        }
        if($no_makes_in_letter)
        {
            $td[$idx] .= "None<br />\n";
        }
        $td[$idx] .= "</td>\n";
    }

    $output = "";
    for($i=0; $idx<13; $i++)
    {
        $output .= "<tr>\n";
        $output .= $td[$idx];
        $output .= $td[$idx+13];
        $output .= "</tr>\n";
    }

} // end if

else
{
    echo "No results";
}

?>

<table class="center" style="text-align:center" width="400px" border="1" cellpadding="5" cellspacing="5">
<tr>
  <td colspan="2">
    <a href="#A">A</a>
    <a href="#B">B</a>
    <a href="#C">C</a>
    <a href="#D">D</a>
    <a href="#E">E</a>
    <a href="#F">F</a>
    <a href="#G">G</a>
    <a href="#H">H</a>
    <a href="#I">I</a>
    <a href="#J">J</a>
    <a href="#K">K</a>
    <a href="#L">L</a>
    <a href="#M">M</a>
  </td>
</tr>
<tr>
  <td colspan="2">
    <a href="#N">N</a>
    <a href="#O">O</a>
    <a href="#P">P</a>
    <a href="#Q">Q</a>
    <a href="#R">R</a>
    <a href="#S">S</a>
    <a href="#T">T</a>
    <a href="#U">U</a>
    <a href="#V">V</a>
    <a href="#W">W</a>
    <a href="#X">X</a>
    <a href="#Y">Y</a>
    <a href="#Z">Z</a>
  </td>
</tr>
<?php echo $output; ?>
</table>

[aliento]

you are fucking experts i really like you and jealous you

Great Incredible you are indeed freaks

[Fluoresce]

And, where did you ask for that? I do like helping people, but there's nothing more frustrating than taking the time to help someone only to have them ask for something different once you provide what they originally asked for.

 

Sorry, dude! I really do appreciate your help. You're a PHP machine! Do you talk in PHP, too!

 

Now, off to test the new code . . .

[Fluoresce]

That's strange . . . There doesn't seem to be any output. There isn't even an error message. When I do a 'View source', all that appears is this:

 

<table class="center" style="text-align:center" width="400px" border="1" cellpadding="5" cellspacing="5">
<tr>
  <td colspan="2">
    <a href="#A">A</a>
    <a href="#B">B</a>
    <a href="#C">C</a>
    <a href="#D">D</a>
    <a href="#E">E</a>
    <a href="#F">F</a>
    <a href="#G">G</a>
    <a href="#H">H</a>
    <a href="#I">I</a>
    <a href="#J">J</a>
    <a href="#K">K</a>
    <a href="#L">L</a>
    <a href="#M">M</a>
  </td>
</tr>
<tr>
  <td colspan="2">
    <a href="#N">N</a>
    <a href="#O">O</a>
    <a href="#P">P</a>
    <a href="#Q">Q</a>
    <a href="#R">R</a>
    <a href="#S">S</a>
    <a href="#T">T</a>
    <a href="#U">U</a>
    <a href="#V">V</a>
    <a href="#W">W</a>
    <a href="#X">X</a>
    <a href="#Y">Y</a>
    <a href="#Z">Z</a>
  </td>
</tr>
</table>

 

Since there's no error message, I created a file with this code:

 

<?php
error_reporting(E_ALL);
ini_set("display_errors", 1);
include("file_with_errors.php");
?>

 

When I ran this file, I got these notices:

 

"Notice: Undefined offset: 0 in [Location] on line 124

 

Notice: Undefined offset: 1 in [Location] on line 124

 

Notice: Undefined offset: 2 in [Location] on line 124"

 

Etc., etc., all the way up to:

 

"Notice: Undefined offset: 25 in [Location] on line 124"

 

The problem is, I don't know which line 124 is. It's blank when I look at my code in Notepad.

[Psycho]

I made a last minute change in some variable names and didn't get them all correct. Change the last loop as follows:

 

    $output = "";
    for($idx=0; $idx<13; $idx++)
    {
        $output .= "<tr>\n";
        $output .= $td[$idx];
        $output .= $td[$idx+13];
        $output .= "</tr>\n";
    }

Still not tested, so there may be more minor errors.

 

As for the error messages, the line numbers refer to the PHP file NOT the HTML output.

[Fluoresce]

MJ, it worked! Everything's working brilliantly.

 

With a great big thank you to you and to anyone else who helped, I hereby declare this thread solved.