Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7

Thread: SQL search help

  1. #1
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    SQL search help

    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.

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,852
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    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?
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

    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)

    PHP Code:
    //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.

    PHP Code:
    $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().

    PHP Code:
    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.

    PHP Code:
    $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.

  • Users who have thanked Fumigator for this post:

    jdwilsh (03-21-2010)

  • #4
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  • #5
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
    Code:
    <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 Code:
    <?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>";

    ?>

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

    PHP Code:
    $data mysql_fetch_assoc($result); 

  • #7
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Its working perfectly

    Thank you so much!

    Now i just need to make it look nice haha


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •