Searching via MySQL

[Saiyaman]

I am going to make a mysql table that has a bunch of data in it. I want to make a script that will allow a user to either click a letter (like on the top of the page it says # A B C . . .) and have it output an alphabatized list of only the letter they click on, IE, click A, it would output Alfred, Apple, Azure. With each of the outputed items also alphabatized if possible. I would also want a search box to search for a specific phrase. How can I go about doing this? And also, MAYBE have an admin panel to add more info through forms, otherwise i can just do it by hand.

[Bree]

I think that's where the PHP comes into play... You are doing exactly what I plan to do soon (Search Fonts for Download by letter) I'm not that far along to be able to help you out (Still tearing my own hair out trying to understand it all) But I'm sure someone will be along shortly to help. Or you can look up buy books that teach you how to write PHP... I got PHP/MySQL for Dummies and it's working slowly but surely. Good Luck

-Bree

[BerksWebGuy]

Remember...I'm not a PHP guru...but...

You're trying to do what I was just playing around with at www.TrackMyCoupons.com (try out the demo...just go to 'try demo' then 'view coupons'...there are only a few samples...so you can add one and it will list it alphabetically).

Anyways...to look at a MySQL table, look by first letter, then sort alphabetically...then it may look like this:

mysql_query("SELECT field FROM table_name WHERE field LIKE '$letter" ORDER BY field);

Your $letter would be a variable that would be entered from the previous page. It would look for names in 'field' that begin with that letter. Also the ORDER BY is what makes it alphabetical.

[Saiyaman]

so, in your example berks, will that sort if it is upper or lower. IE, the person selects A, so $letter = "A", but if the table data says apple, will that be sorted. so is field the column name?

[Fabian]

Remember...I'm not a PHP guru...but...


mysql_query("SELECT field FROM table_name WHERE field LIKE '$letter' ORDER BY field");


But here you have a specific column reserverd for the first letter?

You can also use the following syntax and find the first character in a string field:

mysql_query("SELECT field FROM table_name WHERE field LIKE '$letter%' ORDER BY field");

(the % is a wildcard)

for a phrase you would use the wildcards as well:

mysql_query("SELECT field FROM table_name WHERE field LIKE '%$phrase%' ORDER BY field");

[BerksWebGuy]

Fabian:
You're right...but even without the % (wildcard) it will work for the first letter (from what I tested).

Saiyaman:
I don't think this is case sensitive...it shouldn't be. All of the 'field' is the column name.

[Fabian]

But when you don't use a wildcard it only finds exact matches.

You can do do a case-sensitive query in mysql (just figured that out :cool: ) but it's a bit tricky.

SELECT field FROM table_name WHERE field LIKE CONCAT(CHAR($charachter),'%')

it will search the ascii chracter in (CHAR($charachter). The CONCAT function is to append anything after it.

If you want to use this you must pass the numeric value of a letter as the variable $character.

So (70 is the ascii value of the capital F),

SELECT field FROM table_name WHERE field LIKE CONCAT(CHAR(70),'%')

will give all the entries that begin with F.

[BerksWebGuy]

But when you don't use a wildcard it only finds exact matches.


I have to disagree with you there...since I tested with only the letter and no wildcard...and it brings up any entry with that beginning later (nothing more. nothing less). But it is 'good coding' to put the wildcard in there.

[Metro]

Take a look at this script. Addressbook (http://www.widgetmonkey.com/app.php?id=1) . It has an A_Z name lookup. When I first started using PHP & SQL I learned a lot from it.

[Fabian]

That is very odd then :)

Have a look at http://sqlcourse.com/select.html

this is what they say:

select * from empinfo
where first = 'Eric';
This will only select rows where the first name equals 'Eric' exactly.


And that's how my mySQL queries work as well. :)

edit: hmmmm.... of course '=' is not 'like' :o but still ;)

[Saiyaman]

The Addressbook thing is definately helping. But I was wondering if it is possible to have a form to add/edit/delete somthing, but only have it accessable if you have the correct pw. I have some people that need to add stuff that don't know how to use mySQL.

edit:
decided i may be lazy and just use htaccess

[Saiyaman]

<?
?>

<h1>Edit / add anime entry</h1>
<?php
include ("http://animeoverload.aeforums.net/include/header.inc.php");
include("http://animeoverload.aeforums.net/include/dbconnect.php");
if($submit)
{

$sql = "INSERT INTO $table (name, description, total_eps, encoded_eps, encoded_by, quality, resolution) VALUES ('$name','$description','$total','$encoded','$by', '$quality','$res')";
$result = mysql_query($sql);
echo "<br><br>Information entered into anime list.\n";
}
else if($update)
{
$sql = "UPDATE $table SET name='$name',description='$description',total_eps= '$total',encoded_eps='$encoded',encoded_by='$by',q uality='$quality',resolution='$res' WHERE id=$id";
$result = mysql_query($sql);
echo "<br><br>Anime list updated.\n";
}
else if($id)
{
$result = mysql_query("SELECT * FROM $table WHERE id=$id",$db);
$myrow = mysql_fetch_array($result);
?>
<form method="post" action="edit.php">

<table width="380" border="0" cellspacing="1" cellpadding="1">
<tr>
<td>
<input type="hidden" name="id" value="<?php echo $myrow["id"]?>">
Anime Name: </td>
<td>
<input type="Text" name="name" size="60" value="<?php echo $myrow["name"]?>">
</td>
</tr>
<tr>
<td>Description: </td>
<td>
<textarea name="description" rows="5" cols="60"><?php echo $myrow["description"]?></textarea>
</td>
</tr>
<tr>
<td>Total Episodes: </td>
<td>
<input type="Text" name="total" size="60" value="<?php echo $myrow["total_eps"]?>">
</td>
</tr>
<tr>
<td>Encoded Episodes: </td>
<td>
<input type="Text" name="encoded" size="60" value="<?php echo $myrow["encoded_eps"]?>">
</td>
</tr>
<tr>
<td>Encoded By: </td>
<td>
<input type="Text" name="by" size="60" value="<?php echo $myrow["encoded_by"]?>">
</td>
</tr>
<tr>
<td>Quality: </td>
<td>
<input type="Text" name="quality" size="60" value="<?php echo $myrow["quality"]?>">
</td>
</tr>
<tr>
<td>Resolution: </td>
<td>
<input type="Text" name="resolution" size="60" value="<?php echo $myrow["resolution"]?>">
</td>
</tr>
</table>
<input type="Submit" name="update" value="Update information">
</form>
<?

}
else
{
?>
<form method="post" action="<?php echo $PHP_SELF?>">

<table width="380" border="0" cellspacing="1" cellpadding="1">
<tr>
<td>
<input type="hidden" name="id" value="<?php echo $myrow["id"]?>">
Anime Name: </td>
<td>
<input type="Text" name="name" size="60" value="Name of Anime">
</td>
</tr>
<tr>
<td>Description: </td>
<td>
<textarea name="description" rows="5" cols="60">"Brief description of the anime"</textarea>
</td>
</tr>
<tr>
<td>Total Episodes: </td>
<td>
<input type="Text" name="total" size="60" value="y">
</td>
</tr>
<tr>
<td>Encoded Episodes: </td>
<td>
<input type="Text" name="encoded" size="60" value="x-y">
</td>
</tr>
<tr>
<td>Encoded By: </td>
<td>
<input type="Text" name="by" size="60" value="Encoding Master">
</td>
</tr>
<tr>
<td>Quality: </td>
<td>
<input type="Text" name="quality" size="60" value="400">
</td>
</tr>
<tr>
<td>Resolution: </td>
<td>
<input type="Text" name="resolution" size="60" value="320*240">
</td>
</tr>
</table>
<input type="Submit" name="submit" value="Enter information">
</form>
<?
}
include ("http://animeoverload.aeforums.net/include/footer.inc.php");
?>
<br>
<a href='http://animeoverload.aeforums.net/index.php'>home page</a> | <a href='http://animeoverload.aeforums.net/search.php'>search</a> | <a href='http://animeoverload.aeforums.net/az_index.php'>A-Z</a>



<?
include ("http://animeoverload.aeforums.net/include/header.inc.php");
include("http://animeoverload.aeforums.net/include/dbconnect.php");

mysql_query("DELETE FROM $table WHERE id=$id",$db);
echo "Record deleted<br><br>";
echo "<a href='http://animeoverload.aeforums.net/index.php'>home page</a> | <a href='http://animeoverload.aeforums.net/search.php'>search</a> | <a href='http://animeoverload.aeforums.net/az_index.php'>A-Z</a>";

include ("http://animeoverload.aeforums.net/include/footer.inc.php");
?>


For the life of me, I don't understand why these don't work correctly with the database. It is connecting with the database as far as I know, but the database itself isn't being modified.