SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select returns 0 results

    when i run the following script it returns 0 results from the data base. By accident I duplicated a row in mysql when I query it, it will return both. If I take the duplicate out it returns 0 results. I am learning both php and mysql.

    PHP Script
    <?php
    //Force script errors and warnings to show during production only.
    error_reporting(E_ALL);

    ini_set('display_errors', '1');

    // intialize search_output variable
    $search_output = "";

    if(isset($_POST['searchquery']) && $_POST['searchquery'] != ""){
    // run code if condition meets here
    $searchquery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['searchquery']);


    $sqlCommand = "SELECT * FROM search WHERE keywords ='%$searchquery%'";

    include_once("connect_to_mysql.php");

    $query = mysql_query($sqlCommand) or die(mysql_error());

    $count = mysql_num_rows($query);

    if($count > 1){

    $search_output .= "<hr />$count results for <strong>$searchquery</strong><hr />";

    while($row = mysql_fetch_array($query)){

    $id = $row["id"];
    $keywords = $row["keywords"];
    $title = $row["title"];
    $link = $row["link"];
    $description = $row["description"];

    $search_output .= "Item: $title,<br />
    Description - $description,<br />
    <a href='$link'>$link</a><br /><hr />";

    } // close while

    } else {

    $search_output = "<hr />0 results for <strong>$searchquery</strong><hr />$sqlCommand";

    }


    }

    ?>


    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

    Search: <input name="searchquery" type="text" size="40" maxlength="88">


    <input name="myBtn" type="submit">
    </form>


    Data base


    Host: 127.0.0.1
    Database: search_box
    Generation Time: Dec 03, 2012 at 09:28 PM
    Generated by: phpMyAdmin 3.5.2.2 / MySQL 5.5.27
    SQL query: SELECT * FROM `search` LIMIT 0, 30 ;
    Rows: 7






    id

    title

    description

    keywords

    link




    1

    Paper Rush

    Paper rush is used in seat weaving

    paper rush, fiber rush, fiber cord

    paper_rush.php



    4

    Flat Reed

    Flat reed is used in basket weaving an wicker furniture.

    flat reed, flat flat reed, flat rattan core

    flat_reed.php



    5

    Round Reed

    round reed is used in wicker furniture and basket weaving.

    round reed, center cane, rattan core

    round_reed.php



    6

    Binder Cane

    Binder cane is used for wiker furniture wrappings, closewoven seat weaving and the final step of hand cane.

    binder cane

    binder_cane.php



    7

    Open Mesh Cane

    Open mesh cane is used in seats an backs that have a groove around the frame.

    Open mesh cane, machined cane, pressed cane, cane webbing

    openmesh_cane.php



    8

    Reed Spline

    Reed spline is used to hold the cane in a groove when using cane webbing

    red spline, spline

    reed_spline.php



    10

    Round Reed

    Round reed is used in wicker furniture and basket weaving.

    round reed, center cane, rattan core

    round_reed.php

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by tompat View Post
    By accident I duplicated a row in mysql
    So delete the duplicate
    Using PHPMyAdmin, it's easy deleting a row.
    If I take the duplicate out it returns 0 results.
    What do you mean by "take out"?

    Code:
    SELECT * FROM search WHERE keywords ='%$searchquery%'
    That WHERE condition makes no sense. Use = without the % to get a specific value, or use LIKE and % to get values that contain the search value anywhere.
    For example, if $searchquery contains 'apple', then
    Code:
    keywords = 'apple'
    will return all rows with the value 'apple' in the keywords column, while
    Code:
    keywords IN '%apple%'
    will return all rows with a value in the keywords column that contains 'apple', like 'apple tree', 'apple', 'two apples'.
    Your query
    Code:
    keywords = '%apple%'
    would return something only if there is a row with '%apple%' in the keywords column.

    paper rush, fiber rush, fiber cord
    Comma separated values in a column might mean complications if you want to do stuff with those values, like for example search for them !
    Of course, using LIKE '%$searchquery%' resolves that problem, as long as you don't want to search for exact keywords.

    Edit: I almost forgot! Since you are learning PHP and MySQL, you might want to start learning the mysqli_ extension, because the mysql_ one is not recomended for writing new code.

  3. #3
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I apolgize I have been working on this for 2 solid days. I had the like in there as you stated, but forgot to put it back. I took the commas out of the keywords, and it still does the same thing
    0 results for fiber cord
    --------------------------------------------------------------------------------
    SELECT * FROM search WHERE keywords LIKE '%fiber cord%'

    I duplicated one of the rows again and it works but 2 results
    2 results for cane webbing
    --------------------------------------------------------------------------------
    Item: Open Mesh Cane,
    Description - Open mesh cane is used in seats an backs that have a groove around the frame.,
    openmesh_cane.php

    --------------------------------------------------------------------------------
    Item: Open Mesh Cane,
    Description - Open mesh cane is used in seats an backs that have a groove around the frame.,
    openmesh_cane.php

    Still don't understand why it wont get the results of the row.

    I am using this on xampp on my local machine php5. & mysql 5,

    I noticed in my search the last few days that PDO or mysqli but oracles documentation is not real clear for a beginner, any recomendations as far as books?

    Thank you

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by tompat View Post
    I apolgize I have been working on this for 2 solid days. I had the like in there as you stated, but forgot to put it back. I took the commas out of the keywords, and it still does the same thing
    The problem is not the comma's, it's having multiple values in one column. But it shouldn't be the cause of your problem.
    0 results for fiber cord
    --------------------------------------------------------------------------------
    SELECT * FROM search WHERE keywords LIKE '%fiber cord%'

    I duplicated one of the rows again and it works but 2 results
    2 results for cane webbing
    --------------------------------------------------------------------------------
    Item: Open Mesh Cane,
    Description - Open mesh cane is used in seats an backs that have a groove around the frame.,
    openmesh_cane.php

    --------------------------------------------------------------------------------
    Item: Open Mesh Cane,
    Description - Open mesh cane is used in seats an backs that have a groove around the frame.,
    openmesh_cane.php

    Still don't understand why it wont get the results of the row.
    The problem is in this line
    PHP Code:
    if($count 1){ 
    I am using this on xampp on my local machine php5. & mysql 5,

    I noticed in my search the last few days that PDO or mysqli but oracles documentation is not real clear for a beginner, any recomendations as far as books?

    Thank you
    Oracles documentation? Did you take a look at the PHP documentation I linked to?

  5. #5
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One result is not greater than one!! A different set of eyes sure is great. Yes I have both php & msql documentation on my favorite bar. Thanks a million


Bookmarks

Posting Permissions

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