www.webdeveloper.com
Results 1 to 13 of 13

Thread: Pulling data from mySql table using PHP and AJAX

  1. #1
    Join Date
    Jun 2011
    Posts
    10

    Pulling data from mySql table using PHP and AJAX

    Hi, I am a PHP newbie and need some help with this storelocator I am building.

    I would like to user to select the county they would like to find store located in by using a drop down menu.

    this is the html and AJAX for the user interface
    HTML Code:
    <html>
    <head>
    <script type="text/javascript">
    function showUser(str)
    {
    if (str=="")
      {
      document.getElementById("txtHint").innerHTML="";
      return;
      }
    if (window.XMLHttpRequest)
      {// code for IE7+, Firefox, Chrome, Opera, Safari
      xmlhttp=new XMLHttpRequest();
      }
    else
      {// code for IE6, IE5
      xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
      }
    xmlhttp.onreadystatechange=function()
      {
      if (xmlhttp.readyState==4 && xmlhttp.status==200)
        {
        document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
        }
      }
    xmlhttp.open("GET","getuser.php?q="+str,true);
    xmlhttp.send();
    }
    </script>
    </head>
    <body>
    
    <form>
    <select name="shop" onchange="showUser(this.value)">
    <option value="">Select a County/Region:</option>
    <option value="1">West Midlands</option>
    <option value="2">East Midlands</option>
    <option value="3">South West</option>
    <option value="4">South East</option>
    <option value="5">The North</option>
    <option value="6">Scotland</option>
    <option value="7">Wales</option>
    <option value="8">London</option>
    <option value="9">Herefordshire</option>
    </select>
    </form>
    <br />
    <div id="txtHint"><b>Try one of these outlets or shops</b></div>
    
    </body>
    </html>
    I have a table called store in mySQL db with the following fields: company_name, address, county, country, phone, web_site.

    I would like the user to be able to select e.g. "Herefordshire" and all shops with county = Herefordshire should be displayed. This is the page for the php operation:

    PHP Code:
    <?php
    $q
    =$_GET["q"];

    $con mysql_connect('localhost''shops''pwds123');
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }

    mysql_select_db("shops"$con);

    $sql="SELECT * FROM shop WHERE county = '".$q."'";

    $result mysql_query($sql);

    echo 
    "<table border='1'>
    <tr>
    <th>Company Name</th>
    <th>Address</th>
    <th>City/Town</th>
    <th>County</th>
    <th>Post Code</th>
    <th>Phone</th>
    <th>Web Site</th>
    </tr>"
    ;

    while(
    $row mysql_fetch_array($result))
      {
      echo 
    "<tr>";
      echo 
    "<td>" $row['company_name'] . "</td>";
      echo 
    "<td>" $row['address'] . "</td>";
      echo 
    "<td>" $row['city_town'] . "</td>";
      echo 
    "<td>" $row['county'] . "</td>";
      echo 
    "<td>" $row['post_code'] . "</td>";
       echo 
    "<td>" $row['phone'] . "</td>";
        echo 
    "<td>" $row['web_site'] . "</td>";
      echo 
    "</tr>";
      }
    echo 
    "</table>";

    mysql_close($con);
    ?>
    The above doesn't pull any data - which I assume it because I don't know how to set up the query properly.
    The reason I am using AJAX on the html script is because I found an example on the web but I realise it might not be the best way but am not capable to determine that.

    Appreciate any help or nudge in the right direction. As I said, am a newbie so need some detailed help.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,924
    Your AJAX function in the JavaScript is calling a page called "getuser.php". Is that the correct file? (I'm asking because the PHP file doesn't seem to have anything to do with getting a user. )
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jun 2011
    Posts
    10
    hi nogdog - yes getuser.php is the correct file....

  4. #4
    Join Date
    Jan 2011
    Location
    United Kingdom, London
    Posts
    117
    could you try this?
    Code:
    $sql="SELECT * FROM shop WHERE county = {$q}";
    Javascript, jQuery / Prototype & PHP expert ready to complete your work on Freelancer.co.uk - Hire Me at https://www.freelancer.co.uk/users/1...&action=hireme

  5. #5
    Join Date
    Jun 2011
    Posts
    10
    thanks for taking the time to reply - I tried your suggestion but no luck. I don't get any error messages or warnings either.... Perhaps using java is a bit too convoluted?

  6. #6
    Join Date
    Jan 2011
    Location
    United Kingdom, London
    Posts
    117
    did you tried to get all records from that call?
    Code:
    $sql="SELECT * FROM shop";
    does it return anything?

    change that code too to check if everything is ok:
    Code:
    if (xmlhttp.readyState==4 && xmlhttp.status==200)
        {
        document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
        }else{
    alert('something is wrong');
    }
    try to start your PHP file directly: getuser.php?q=[your value] and see if there is any result
    Last edited by maurycy; 06-13-2011 at 06:41 AM. Reason: an IDEA!
    Javascript, jQuery / Prototype & PHP expert ready to complete your work on Freelancer.co.uk - Hire Me at https://www.freelancer.co.uk/users/1...&action=hireme

  7. #7
    Join Date
    Jun 2011
    Posts
    10
    good idea - Yes
    PHP Code:
    $sql="SELECT * FROM shop"
    returns everything so should be nearly there......

  8. #8
    Join Date
    Jan 2011
    Location
    United Kingdom, London
    Posts
    117
    did you tried other suggestions?
    Javascript, jQuery / Prototype & PHP expert ready to complete your work on Freelancer.co.uk - Hire Me at https://www.freelancer.co.uk/users/1...&action=hireme

  9. #9
    Join Date
    Jun 2011
    Posts
    10

    Question

    ...hmm did just now..and when I change the script to
    HTML Code:
    <html>
    <head>
    <script type="text/javascript">
    function showUser(str)
    {
    if (str=="")
      {
      document.getElementById("txtHint").innerHTML="";
      return;
      }
    if (window.XMLHttpRequest)
      {// code for IE7+, Firefox, Chrome, Opera, Safari
      xmlhttp=new XMLHttpRequest();
      }
    else
      {// code for IE6, IE5
      xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
      }
    xmlhttp.onreadystatechange=function()
      {
      if (xmlhttp.readyState==4 && xmlhttp.status==200)
        {
        document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
        }
      } else{
    alert('something is wrong');
    }
    xmlhttp.open("GET","getuser.php?q="+str,true);
    xmlhttp.send();
    }
    </script>
    it goes back to not pulling any data at all....

  10. #10
    Join Date
    Jun 2011
    Posts
    10
    With some great tips from above author I managed to narrow it down and by

    changing:
    HTML Code:
    <option value="8">London</option>
    <option value="9">Herefordshire</option>
    to:
    HTML Code:
    <option value="london">London</option>
    <option value="herefordshire">Herefordshire</option>
    it is now working fine.

    Installing Firebug helped be see what the query I was using was trying to do.

  11. #11
    Join Date
    Jan 2011
    Location
    United Kingdom, London
    Posts
    117
    hehe, so you was sending wrong value
    have a nice day
    Cheers
    Javascript, jQuery / Prototype & PHP expert ready to complete your work on Freelancer.co.uk - Hire Me at https://www.freelancer.co.uk/users/1...&action=hireme

  12. #12
    Join Date
    Jun 2011
    Posts
    10
    right, I would now like to modify this script so that the html dropdown menu automatically pulls any county that has been added to the database (at the moment I have to add the counties manually to the dropdown). Here is the code for the html page
    PHP Code:
    <script type="text/javascript">
    function 
    showUser(str)
    {
    if (
    str=="")
      {
      
    document.getElementById("txtHint").innerHTML="";
      return;
      }
    if (
    window.XMLHttpRequest)
      {
    // code for IE7+, Firefox, Chrome, Opera, Safari
      
    xmlhttp=new XMLHttpRequest();
      }
    else
      {
    // code for IE6, IE5
      
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
      }
    xmlhttp.onreadystatechange=function()
      {
      if (
    xmlhttp.readyState==&& xmlhttp.status==200)
        {
        
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
        }
      }
    xmlhttp.open("GET","getuser.php?q="+str,true);
    xmlhttp.send();
    }
    </script>

    </head>
    <body>

    <form>
    <select name="shop" class="output" onChange="showUser(this.value)">
    <option value="">Select a County/Country:</option>
    <option value="bristol">Bristol</option>
    <option value="edinburgh">Edinburgh</option>
    <option value="inverness">Inverness</option>
    <option value="largs">Largs</option>
    <option value="somerset">Somerset</option>
    <option value="suffolk">Suffolk</option>
    <option value="Tyne and Wear">Tyne and Wear</option>
    <option value="west sussex">West Sussex</option>
    <option value="international">**International**</option>
    </select>
    </form>
    <br />
    <div id="txtHint"><b></b></div>

    </body>
    </html> 
    and here is the code from the referenced getuser file:

    PHP Code:
    php
    $q
    =$_GET["q"];

    $con mysql_connect('localhost''dbname''dbpwd');
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }

    mysql_select_db("dbname"$con);

    $sql="SELECT * FROM shop WHERE county = '".$q."'";

    $result mysql_query($sql);

    echo 
    "<table width='775' border='0'>
    <tr>
    <th align=left>Name</font></th> 
    <th align=left>Address</th>
    <th align=left>Town</th>
    <th align=left>Country</th>
    <th align=left>Post Code</th>
    <th align=left>Phone</th>
    <th align=left>Web Site</th>
    </tr>"
    ;

    while(
    $row mysql_fetch_array($result))
      {
      echo 
    "<tr>";
      echo 
    "<td>" $row['company_name'] . "</td>";
      echo 
    "<td>" $row['address'] . "</td>";
      echo 
    "<td>" $row['city_town'] . "</td>";
      echo 
    "<td>" $row['region'] . "</td>";
      echo 
    "<td width='80'>" $row['post_code'] . "</td>";
      echo 
    "<td>" $row['phone'] . "</td>";
      echo 
    "<td>" $row['web_site'] . "</td>";
      echo 
    "</tr>";
      }
    echo 
    "</table>"

    mysql_close($con);
    ?> 

    Many Thanks in advance for any answer
    Last edited by olaand; 05-10-2012 at 06:02 AM.

  13. #13
    Join Date
    Jan 2011
    Location
    United Kingdom, London
    Posts
    117
    Basicly what you want to do is populate SELECT box with data existing in database and you can do that in many ways, I'll mention two main solution:
    SELECT content is generated on server side in country.php

    PHP Code:
    $mysql mysql_query("SELECT Country FROM country_table ORDER BY Country DESC");
    $html ''
    while($row mysql_fetch_assoc($mysql)){
        
    $html .= '<option value="'.$row['Country'].'">'.$row['Country'].'</option>';
    }
    echo 
    $html
    that will generate list like this one:
    <option value="bristol">Bristol</option>
    <option value="edinburgh">Edinburgh</option>
    <option value="inverness">Inverness</option>
    <option value="largs">Largs</option>
    <option value="somerset">Somerset</option>
    <option value="suffolk">Suffolk</option>
    <option value="Tyne and Wear">Tyne and Wear</option>
    <option value="west sussex">West Sussex</option>

    then all you need to do is add that to your desired SELECT box

    other solution is to export JSON object with Country/County names and then manipulate it from JS level, but i didn't saw any JS framework in your code so i suppose first solution will fit you.


    BTW:
    PHP Code:
    $sql="SELECT * FROM shop WHERE county = '".$q."'"
    bad bad bad bad bad, never EVER trust users, they are all filthy beasts waiting for mysql injection
    Javascript, jQuery / Prototype & PHP expert ready to complete your work on Freelancer.co.uk - Hire Me at https://www.freelancer.co.uk/users/1...&action=hireme

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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
  •  
HTML5 Development Center



Recent Articles