[PHP + MYSQL]Getting first character of a value in a table

[evileks]

So I have a system that allows a user to submit a game review. This works great and all, but I want to make it so a page will list all my table entrees (reviews) that start with a certain letter. For example a page that was mean't to list all reviews starting with the letter A, what would be the correct way of doing this? I know I want to do "WHERE" or "ORDER BY" but I don't know how to the exact syntax for calling just the reviews with the first letter of the review.

I know one method would be to make a new value called "Section" and have the sections be A-Z allowing the user to pick the section, but I would prefer not to do this.

Any help would be appreciated!

[ya3]

$result = mysql_query("SELECT DISTINCT SUBSTRING(`title`, 1, 1) AS `firstletter` FROM `reviews` ORDER BY
`firstletter`);
while ($row = mysql_fetch_object($result)) {
echo "$row->firstletter<br>";
}

It's a start... I have no idea where to go from here

EDIT: Perhaps this could work... I dunno:


//display all reviews starting with 'A'
$result = mysql_query("SELECT DISTINCT SUBSTRING(`title`, 1, 1) AS `firstletter` FROM `reviews` WHERE `firstletter` = 'a' ORDER BY `firstletter`);
$numrows = mysql_num_rows($result);
$result2 = mysql_query("SELECT * FROM `reviews` LIMIT 0,'$numrows'");
while ($row = mysql_fetch_object($result2)) {
echo "$row->title<br>";
}
:P

[mlk]

Well if you fetch rows normally they will be listed alphabetically

$query = "SELECT * FROM table ORDER BY reviews ASC"
So you could just do some array sorting with all the reviews.

You can also use substring in the mysql query, but I'm not sure which method uses the most cpu cycles:


$currentletter = "'a'";
$query = "SELECT * FROM table WHERE SUBSTRING(reviews,1,1) = ".$currentletter." ORDER BY reviews ASC";


edit: well, i was beaten to it =)

[ya3]

$currentletter = "'a'";
$query = "SELECT * FROM table WHERE SUBSTRING(reviews,1,1) = ".$currentletter." ORDER BY reviews ASC";
Use this one :thumb:

[Seb Hughes]

^Make currentlatter a get varible so its easy to have a-z for displaying, ANd put all letters in an array and use ergri(grrr cant spell) to check if it is in the array if not that echo a message, remeber to stop mysql injection when using get varibles

[evileks]

I tried your code mlk, but I can't seem to get it working. The code I'm using is:

<b> Reviews starting with the letter:
<?php
$currentletter = $HTTP_GET_VARS['letter'];
echo "$currentletter";?> </b><br>
<?php
// Require the database class
require_once('../includes/DbConnector.php');

// Create an object (instance) of the DbConnector
$connector = new DbConnector();

// Execute the query to retrieve articles
$result = $connector->query('SELECT * FROM reviews WHERE SUBSTRING(title,1,1) = ".$currentletter." ORDER BY title ASC');

// Get an array containing the results.
// Loop for each item in that array
while ($row = $connector->fetchArray($result)){

echo '<p><a href="review.php?id='.$row['ID'].'">';
echo $row['title'];
echo '</a><br>';
echo '</p>';

}
?>

You can see it in action at:

http://www.razergames.com/ludnix/test/reviews/reviewlist.php?letter=A

It's strange because I know for a fact that there is a game review in the database that's title begins with the letter A.

[hl]

Perhaps the case of id?
edit:/ Oh wait... you used a fetch_array I guess. I don't know the parameters you used since its in a function of one of your classes, however you might want to use mysql_fetch_assoc if you want to use $row[ID]

[evileks]

Perhaps the case of id?
edit:/ Oh wait... you used a fetch_array I guess. I don't know the parameters you used since its in a function of one of your classes, however you might want to use mysql_fetch_assoc if you want to use $row[ID]
The function does this as defined in Dbconnector:


function fetchArray($result) {
return mysql_fetch_array($result);
}
The code works fine when I don't do the "WHERE" substring part. You can see it working here: http://www.razergames.com/ludnix/test/reviews/reviewlist2.php

but, once I have the where substring part it doesn't get any results :(

[Ankou]

For your query, change your ' (single quotes) to " (double quotes) and give that a try.


$result = $connector->query("SELECT * FROM reviews WHERE SUBSTRING(title,1,1) = ".$currentletter." ORDER BY title ASC");

[evileks]

Okay it's working now. Heres the code for anyone who wants to know:

Seems to have been just an error in what " ' to use.


<b> Reviews starting with the letter:
<?php
$currentletter = $HTTP_GET_VARS['letter'];
echo "$currentletter";?> </b><br>
<?php
// Require the database class
require_once('../includes/DbConnector.php');

// Create an object (instance) of the DbConnector
$connector = new DbConnector();

// Execute the query to retrieve articles
$result = $connector->query("SELECT * FROM reviews WHERE SUBSTRING(title,1,1) = '$currentletter' ORDER BY title ASC");
if (!$result){
echo 'No results';}

// Get an array containing the results.
// Loop for each item in that array
while ($row = $connector->fetchArray($result)){

echo '<p><a href="review.php?id='.$row['id'].'">';
echo $row['title'];
echo '</a><br>';
echo '</p>';

}
?>