PHP / MySQL code only works halfway through

[jd307]

Hi all.

 

I have been creating a website for my university project.  This is pretty much a self-contained project where I have been teaching myself the relevant PHP and SQL coding as I need it, so I am only really a beginnger. 

 

Now, there is probably a better way to do this than how I am doing it, but this is the only way I can think of getting the results.  The website is a site where members can submit their artwork online.  I have a page called artists.php which literally just lists all of the members it has in alphabetical order.  It should produce a result such as:

 

A

 

amember1

amember2

 

B

 

bmember1

bmember2

 

So there is a <h3> tag displaying the current letter, followed by all usernames that begin with that letter.  Here is my code for doing this:

 

	<h3>A</h3>
<?php

include('connect.php');

// Obtain all of the usernames that begin with the letter A.
$result = mysql_query("SELECT * FROM member WHERE username LIKE 'A%'") or die(mysql_error());

// Fetches usernames one by one until the list of users has ended
while($row = mysql_fetch_array($result))
{
	// Displays each username as a hyperlink to the user's profile page
	echo "<a href=\"profile.php?user=" . $row['username'] . "\">" . $row['username'] . "</a><br />";
}
?>
<h3>B</h3>
	<?php
// Obtain all of the usernames that begin with the letter A.
$result = mysql_query("SELECT * FROM member WHERE username LIKE 'B%'") or die(mysql_error());

// Fetches usernames one by one until the list of users has ended
while($row = mysql_fetch_array($result))
{
	// Displays each username as a hyperlink to the user's profile page
	echo "<a href=\"profile.php?user=" . $row['username'] . "\">" . $row['username'] . "</a><br />";
}
?>

 

As you can see, this only shows letters A and B, but I repeat the same code and query down to the letter Z. 

 

This runs fine (showing results for letter D as there are no results for A B or C at the moment) however the page stops running at the letter E with the following error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1.

 

I have looked for mistakes in the code, but it was all copy and pasted.  I can only imagine if the server is having problems running the queries so quickly, but wouldn't have thought so.

 

If anyone could shed a little light on the situation and where I should be looking to solve this, it would be much appreciated!

 

Thanks.

[PFMaBiSmAd]

If you just query for all the data and do an ORDER BY username, your data will be retrieved in the correct order. It is then just a simple matter for your presentation code to detect a change in the first letter and output the  <h3>...</h3> heading.

 

This will save you from duplicating code that only varies in what data it operates on, eliminating the typing mistakes that cause syntax errors (your current problem in the code for the letter "C".)

[jd307]

Why didn't I think of that?  Haha. Thanks mate!  I guess it's because it's pretty late here.

 

Only question is, the code I have written, SHOULD it work but isn't?  Or would the method of having 26 odd queries cause a problem?  From how I created them, theoretically it should work... I thought.  I will use your method, but would be interested to know if my way should work?  I use a shared server... could that cause an issue?

 

 

Thanks for the quick response

[PFMaBiSmAd]

Every query requires overhead to pass that query to the mysql server, execute the query, and return the results. Executing one query to return 1000 rows is always much faster than executing 10 queries each returning 100 rows.

 

And in the time from my last post to now, I was able to modify your code (untested), which I am sure took only a fraction of the time it took you to create 26 copies -

 

<?php
// note: the following will skip headings for any letter that there are no rows in the database

include('connect.php');
   
// Obtain all of the usernames in alphabetical order.
$result = mysql_query("SELECT * FROM member ORDER BY username") or die(mysql_error());

$last_letter = ""; // initialize variable to hold the previous letter
// Fetches usernames one by one until the list of users has ended
while($row = mysql_fetch_array($result))
{
$current_letter = strtoupper(substr($row['username'], 0,1)); // get the first letter in UC
if($last_letter != $current_letter){
	// output header for the current letter
	echo "<h3>$current_letter</h3>";
	// save the current letter as the last letter
	$last_letter = $current_letter;
}
      // Displays each username as a hyperlink to the user's profile page
      echo "<a href=\"profile.php?user=" . $row['username'] . "\">" . $row['username'] . "</a><br />";
}
?>

 

This has the additional advantage of having only one copy of the presentation code so that if you needed to change or style the letter heading or the link, you would only have to make the change in one place.

 

Edit: for the sql syntax error you are currently getting, you would need to post the code containing the query to get specific help with what is wrong with it.

[jd307]

Thank you PFMaBiSmAd!

 

I have looked through your code and pretty much understand what is going on.  I have never really used or encountered strtoupper or substr.  But after looking through your suggested code and a little look on PHP.net, it makes perfect sense to me.

 

Tried and tested, it is working wonders.  Of course, like you said, it does not give the full A-Z list, but I think I am happy with what the code does.

 

Thanks again mate.

[PFMaBiSmAd]

Version which prints all letter headings -

 

<?php
include('connect.php');

// Obtain all of the usernames in alphabetical order.
$result = mysql_query("SELECT * FROM member ORDER BY username") or die(mysql_error());

$last_letter = "@"; // initialize variable to hold the previous letter (@ is one less than A)
// Fetches usernames one by one until the list of users has ended
while($row = mysql_fetch_array($result))
{
$current_letter = strtoupper(substr($row['username'], 0,1)); // get the first letter in UC
while($last_letter != $current_letter){
	$last_letter = chr(ord($last_letter) + 1);
	echo "<h3>$last_letter</h3>";
}
      // Displays each username as a hyperlink to the user's profile page
      echo "<a href=\"profile.php?user=" . $row['username'] . "\">" . $row['username'] . "</a><br />";
}
// print remainder of headins if last letter was not Z
while($last_letter < 'Z'){
$last_letter = chr(ord($last_letter) + 1);
echo "<h3>$last_letter</h3>";
}
?>