...

View Full Version : SQL search help



jdwilsh
03-19-2010, 04:33 PM
Hi guys

Im really quite new to PHP coding. I know how to use SQL and i'm learning JavaScript at the moment but havent really touched PHP.

I want to make a script to help me out at work basically. I work in a call centre and people phone me up with a code and i need a quick easy way of recognising that code is valid. I have a database with just under 500 rows on it and i need to help me search through it. for example, if i type in 0400 ino the search box it will bring up that row and give me the name address tel number etc from it.

I have tried a few PHP codes i have found on the internet but they have only shown me how many rows contain that number and dont specifically give me the information from that row.

Can anybody point me in the right direction of a decent script that would help me with this?

to cut it short, i search for 0400 and it shows me 'name' 'address' 'telno' from the database.

abduraooft
03-19-2010, 04:41 PM
I want to make a script to help me out at work basically. I work in a call centre and people phone me up with a code and i need a quick easy way of recognising that code is valid. I have a database with just under 500 rows on it and i need to help me search through it. for example, if i type in 0400 ino the search box it will bring up that row and give me the name address tel number etc from it.

I have tried a few PHP codes i have found on the internet but they have only shown me how many rows contain that number and dont specifically give me the information from that row. What kind of DB is this? Could you post the code that you've so far?

Fumigator
03-19-2010, 04:57 PM
Usually I just tell people to google "mysql php tutorial" but today for some strange reason I'll offer a bit of info that may be helpful. :p

I'm assuming your database is a MySQL database. To pull data out of a MySQL database using PHP, you use a 5-step process.

Step 1: Connect to the database.

http://us3.php.net/manual/en/function.mysql-connect.php

Step 2: Write your query. With a specific search as you want to do, your query will be made up of a SELECT command using syntax that will never change, and a variable that contains the value you entered from a browser form. (I recommend you assign your query syntax to a variable using PHP string syntax)



//The variable $code has been assigned your "0400" value prior to this statement
$query = "SELECT name, address, telno FROM yourtable WHERE code = $code";


Step 3: Process the query. Note this step does NOT return any data from your table-- it returns a resource that you can refer to in Step 5.



$result = mysql_query($query);


Step 4: CHECK THE CALL TO mysql_query() FOR ERRORS!! This step is where you will find out if your query syntax is valid and if MySQL was able to understand your query and give you the data you are after. You simply check the $result variable and if it's FALSE, that means the query failed. Notice also I'm calling a function called mysql_error(), which is a handy function that returns the text of the error of the last query processed by mysql_query().



if (!$result) {
die("Query Error! Your query syntax is: $query <br />The error is: ".mysql_error());
}


Step 5: Finally, you're ready to get (fetch) your data. There are several functions in PHP you can use to fetch your data; I usually use mysql_fetch_assoc(), because it returns an associative array and that kind of array is convenient to work with.



$data = mysql_fetch_assoc();
echo "name: " . $data['name'];
echo "address: " . $data['address'];
echo "telno: " . $data['telno'];


Now, most times you fetch data from a query, there will be more than one row in the resultset. In those cases, it makes sense to build a loop to handle the multiple rows. Every time you call mysql_fetch_assoc(), it fetches the next row in the set (just like reading a file). But for your specific requirement here, you only need one row because (presumably) you are selecting based on the primary unique key of the table so only one row will match the value you entered.

jdwilsh
03-21-2010, 09:49 PM
Thanks Fumigator :)

I shall have a go at doing it that way and come back to you. It is a MySQL DB sorry, forgot to mention.

jdwilsh
03-22-2010, 01:42 AM
Ok, i've managed to get it connecting to my DB and i made it echo back that it was connecting ok from an example in the link you gave me in step 1!

When it came to the code, i have had a play around with a few mixes of things. Code i found on tutorials and code from your advice. I've split it up into 2 files, a html for the search box which then links in with the php.

I am getting this error though

Warning: Wrong parameter count for mysql_fetch_assoc() in /home/jdwilsh1/public_html/testing/a.php on line 30

line 30 being: $data = mysql_fetch_assoc();

could you help me in where im going wrong please? here is my code

search.html

<HTML>
<TITLE>Store Code search</TITLE>
<BODY>

<form name="form" action="a.php" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
</form>

</BODY>
</HTML>

a.php

<?php

$var = @$_GET['q'];
$trimmed = trim($var);

$limit=10;

if ($trimmed == "")
{
echo "Please enter a search";
exit;
}

if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}

mysql_connect("localhost", "jdwilsh1_store", "storez") or die(mysql_error());
mysql_select_db("jdwilsh1_store") or die(mysql_error());
$query = "select * from stores where sid like \"%$trimmed%\" order by sid";

$result = mysql_query($query);

if (!$result) {
die("Query Error! Your query syntax is: $query <br />The error is: ".mysql_error());
}

$data = mysql_fetch_assoc();
Print "<i>Store ID:</i> ".$data['sid'] . "<BR>";
Print "<i>Location:</i> ".$data['storename'] . "<BR>";
Print "<i>Tel No:</i> ".$data['telnumber'] . "<BR><BR>";
Print "<i>Address 1:</i> ".$data['address1'] . "<BR>";
Print "<i>Address 2:</i> ".$data['address2'] . "<BR>";
Print "<i>Address 3:</i> ".$data['address3'] . "<BR>";
Print "<i>County:</i> ".$data['county'] . "<BR>";
Print "<i>Postcode:</i> ".$data['postcode'] . "<BR>";
Print "<i>E-Mail:</i> <a href=\"mailto:".$data['email'] . ">".$data['email'] . "</a><BR><BR>";
Print "<i>Monday:</i> ".$data['mono'] . " - ".$data['monc'] . "<BR>";
Print "<i>Tuesday:</i> ".$data['tueo'] . " - ".$data['tuec'] . "<BR>";
Print "<i>Wednesday:</i> ".$data['wedo'] . " - ".$data['wedc'] . "<BR>";
Print "<i>Thursday:</i> ".$data['thuo'] . " - ".$data['thuc'] . "<BR>";
Print "<i>Friday:</i> ".$data['frio'] . " - ".$data['fric'] . "<BR>";
Print "<i>Saturday:</i> ".$data['sato'] . " - ".$data['satc'] . "<BR>";
Print "<i>Sunday:</i> ".$data['suno'] . " - ".$data['sunc'] . "<BR>";

?>

Fumigator
03-22-2010, 03:11 AM
Aggh I can't believe I made THAT mistake... sorry about that! That's what untested code does for you.

Anyway, you have to send the query resource that was returned to you from you mysql_query() call.



$data = mysql_fetch_assoc($result);

jdwilsh
03-22-2010, 04:26 PM
Its working perfectly :)

Thank you so much!

Now i just need to make it look nice haha



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum