Fetching Data

[xarnet]

I will first explain the current problem followed by the PHP code I am currently using.

I am attempting to display a list of Anime (Japanese Cartoons) that are in one of my tables (animetitle) from my database. Now I only want say Anime beginning with "A" to be displayed. The way I have set up my table is so that:
All anime that start with anything other than a letter will be id=1
All anime that start with A will be id=2
All anime that start wtih B will be id=2
....
All anime that start with Z will be id=27

I have done this so that when the people are viewing the list it is broken down into letters (using whatever.php?id=1, etc).

Now the problem is that in my database if I add a new anime it will not be in the order I want displayed, so what I was curious to know is if there is a way to make them organised alphabetically, as the code I am using right now (below) simply displays them.

<?php

$db = mysql_connect("localhost", "username", "password");
if (!$db) { 
     echo( "<p>Unable to connect to the " . 
           "database server at this time.</p>" ); 
     exit(); 
} 
mysql_select_db("xarnet_com",$db);
if (! @mysql_select_db("xarnet_com") ) { 
     echo( "<p>Unable to locate the " . 
           "database at this time.</p >" ); 
     exit(); 
} 
$query = @mysql_query("SELECT * FROM animetitle WHERE id = '$id'");
if (!$query) {
    echo( "<p>An error occured in the query</p>" ); 
     exit();
}
while ($data = @mysql_fetch_object($query)) {
    echo $data->anime;
}
?>

Any help on this problem would be much appreciated.

--- Note ---
I think I may have posted this in the wrong forum (yet again). If a moderator, super moderator, admin, etc, see this it would most likely be best in the PHP Programming forum [ http://www.sitepointforums.com/forumdisplay.php?forumid=34 ]. Sorry for the inconvenience.

[blindeddie]

There are SQL statements that will actually do what you want without having to number the records with an ID field. First of all instead of:

SELECT * FROM animetitle WHERE id = '$id'

use

SELECT * FROM animetitle WHERE anime_column_name LIKE 'A%'

This will select all the titles that begin with the letter 'A'
(of course replace anime_column_name with the actual column name)

By replacing the letter with a different letter you can select titles that begin with any letter.

to put the records in alpha order, use the ORDER clause, so if you continue to use your original query, it would look like this:

SELECT * FROM animetitle WHERE id = '$id' ORDER BY anime_column_name

or if you decide to use the LIKE clause it would look like this.

SELECT * FROM animetitle WHERE anime_column_name LIKE 'A%' ORDER BY anime_column_name

The ORDER BY clause allows you to order the recordset by any column name that is included in the select portion of the query.

I hope this is clear, if not, let me know.

Ed Warren

[xarnet]

Thanks, that helped a lot, however just one more quick questions. Say for example an anime was called:
.hack//Sign
3x3 Eyes
12 kokuki
801 TTS Airbats
etc
How would I use the "Like" command as of course none of the 'A%', 'B%', etc would work.

[xarnet]

In addition I wish to incorporate a table into the loop, a table that is as follows:

<table cellpading="1" id="con">
<tr>
<td width="410" bgcolor="#FFF8F8">$data->anime<td>
<td width="60" bgcolor="#FFF8F8"><center>$data->series</center></td>
<td width="50" bgcolor="#FFF8F8"><b><center>$data->rating</center></b></td>
</tr>
</table>

This of course I want to be found (where it says HERE):

$query = @mysql_query("SELECT * FROM animegenre WHERE id = '$id' ORDER BY title");
if (!$query) {
    echo( "<p>Unable to connect to the " . 
           "database server at this time.</p>" ); 
     exit();
}
while ($data = @mysql_fetch_object($query)) {
	echo "HERE"

}

I came up with something like this:

$query = @mysql_query("SELECT * FROM animegenre WHERE id = '$id' ORDER BY title");
if (!$query) {
    echo( "<p>Unable to connect to the " . 
           "database server at this time.</p>" ); 
     exit();
}
while ($data = @mysql_fetch_object($query)) {
	echo "<table cellpading=\"1\" id=\"con\">\n";
	echo "<tr>\n";
	echo "<td width=\"410\" bgcolor=\"#FFF8F8\">\n"
	echo $data->anime;
	echo "<td>\n";
	echo "<td width=\"60\" bgcolor=\"#FFF8F8\"><center>\n";
	echo $data->series;
	echo "</center></td>\n";
	echo "<td width=\"50\" bgcolor=\"#FFF8F8\"><b><center>\n";
	echo $data->rating;
	echo "</center></b></td>\n";
	echo "</tr>\n";
	echo "</table>\n";

}

Although I know I am doing something completely wrong (I am still extremely new to php).

Note: I receive the following error:

Parse error: parse error, expecting ','' or';'' in /home/virtual/site15/fst/var/www/html/anime/animegenre.php on line 40

(Line 40 is the line where the $data->anime is located).

--- ALSO ---
I do not expect anyone to write the code for me, so if it's a more complicated problem perhaps a link to a site that better explains how to use HTML within PHP code would be very helpful. Thanks in advance for your time.

[xblue]

I don't see anything wrong with the code you posted, but generally there are different ways of dealing with it:

• have php output html with echo or print statements. then you need to carefully escape any quotes within the html (which can be painful).

• escape from the php mode to output plain html, like

  <?php
  while ($data = @mysql_fetch_object($query)) {
  ?>
  <html>
  <html>
  <html>
  <?php
  }
  // php again
  ?>

• especially for larger projects it may pay off using some kind of templating systems to separate code from design.

[blindeddie]

In the table code you posted , you have left out a semicolon on one of the echo lines.

while ($data = @mysql_fetch_object($query)) {
echo "<table cellpading=\"1\" id=\"con\">\n";
echo "<tr>\n";
echo "<td width=\"410\" bgcolor=\"#FFF8F8\">\n" //semicolon is missing on this line.
echo $data->anime;
echo "<td>\n";
echo "<td width=\"60\" bgcolor=\"#FFF8F8\"><center>\n";
echo $data->series;
echo "</center></td>\n";
echo "<td width=\"50\" bgcolor=\"#FFF8F8\"><b><center>\n";
echo $data->rating;
echo "</center></b></td>\n";
echo "</tr>\n";
echo "</table>\n";

}

Xblue's example would be the way that I would do it, so you might want to try it that way. I will expand on his example so you can see exactly how it would look.

<table>

<?php
while ($data = @mysql_fetch_object($query)) {
?>
<tr>
<td><?PHP echo echo $data->anime;?></td>
</tr>
<tr>
<td><?PHP echo echo $data->series;?></td>
</tr>
<tr>
<td><?PHP echo echo $data->rating;?></td>
</tr>

<?php
}
// php again
?>
</table>

Note in my example, that the table tags are outside the loop I do this so that only one table is created but will grow based on how many results are returned in the recordset. You are free to do it any way you want, but I find this way a little cleaner. In your case the numbering system is betterdue to the Many names that the Anime can have.

[xarnet]

Thanks for all the help, it's now working perfectly ^_