Searching database through PHP

[tubesnube]

Hi, wondering can I get some help here please as this is incredibly frustrating. I have a database that contains files and I want to be able to search those files by putting in a ID number. In my databse my doc_id is the primary key, and I want the user to be able to enter a document ID and have all the information returned to them.

Can anyone help me here as I don't understand why it isn't working? When I enter a document ID into my search page I get the same result each time which is document number 338 for example.

<?php

///-----------------------------
/// Inserting data into database
///-----------------------------

include 'connect_db.php';
include 'newheader.php';

function sanitize_data($data)
    {
        $data = array_map('trim',$data);
        $data = array_map('strip_tags',$data);
        $data = array_map('htmlspecialchars',$data);
        $data = array_map('mysql_real_escape_string',$data);
        return $data;
    }
    $post = sanitize_data($_POST);

if ($_POST['searchID'])
{

    $searchById = "SELECT * FROM tc_tool.forms WHERE doc_id LIKE '%$doc_id%'"; 

    $result = mysql_query($searchById);

    $row = mysql_fetch_assoc($result);

    if (!mysql_query($searchById, $connection))
    {
        echo "Query failed: $query<br />" . mysql_error() . " <br /><br /> ";
    }


    echo     $row  ['doc_id'] .             " <br /> "
            . $row ['doc_title'] .          " <br /> "
            . $row ['doc_number'] .         " <br /> "
            . $row ['doc_type'] .           " <br /> " 
            . $row ['revision'] .           " <br /> " 
            . $row ['cdm_link'] .           " <br /> "
            . $row ['mars_link'] .          " <br /> "
            . $row ['checklist_link'] .     " <br /> "
            . $row ['link_internal_1_3'].   " <br /> "
            . $row ['impacted_products'].   " <br /> "
            . $row ['scope'].               " <br /> "
            . $row ['impacted_products_2'] ." <br /> "
            . $row ['review_class'].        " <br /> "
            . $row ['full_simplified'] .    " <br /> "
            . $row ['pref_earliest'] .      " <br /> "
            . $row ['pref_latest'] .        " <br /> "
            . $row ['prev_reviews'] .       " <br /> "
            . $row ['proj_name'] .          " <br /> "
            . $row ['auth_name'] .          " <br /> "
            . $row ['req_list'] .           " <br /> "
            . $row ['optional_list'] .      " <br /> "
            . $row ['information_only'] .   " <br /> "
            . $row ['chairperson'] .        " <br /> "
            . $row ['req_reviewers'] .      " <br /> "
            . $row ['review_duration'] .    " <br /> "
            . $row ['document_abstract'] .  " <br /> ";

            echo "<br / >";      
}
?>

<?php include "footer.php" ?>

[broj1]

You are probably missing an assignment to a variable $doc_id (put it on line 22) just before you construct $searchById, like:

$doc_id = $_POST['searchID']

And if $doc_id is a number (integer) you could use = instead of LIKE in your query.

[tubesnube]

Thanks broj1 for the help. I've changed it now so it looks like this -

if ($_POST['searchID'])
{
    $doc_id = $_POST['searchID'];
    /// Upper is used to check for uppercase letters
    $searchById = mysql_query("SELECT * FROM tc_tool.forms WHERE upper($doc_id) = '%$find%'"); 

    /*if (!mysql_query($selectById, $connect_db))
        {
            echo "Query failed: $query<br />" . mysql_error() . " with query " . $searchById;
        }
    */
    //while($row = mysql_fetch_array($searchById))
    while ($result = mysql_fetch_array($searchById))
    {
        echo $row  ['doc_id'] .             " <br /> " 
            . $row ['doc_title'] .          " <br /> "
            . $row ['doc_number'] .         " <br /> "
            . $row ['doc_type'] .           " <br /> " 
            . $row ['revision'] .           " <br /> " 
            . $row ['cdm_link'] .           " <br /> "
            . $row ['mars_link'] .          " <br /> "
            . $row ['checklist_link'] .     " <br /> "
            . $row ['link_internal_1_3'].   " <br /> "
            . $row ['impacted_products'].   " <br /> "
            . $row ['scope'].               " <br /> "
            . $row ['impacted_products_2'] ." <br /> "
            . $row ['review_class'].        " <br /> "
            . $row ['full_simplified'] .    " <br /> "
            . $row ['pref_earliest'] .      " <br /> "
            . $row ['pref_latest'] .        " <br /> "
            . $row ['prev_reviews'] .       " <br /> "
            . $row ['proj_name'] .          " <br /> "
            . $row ['auth_name'] .          " <br /> "
            . $row ['req_list'] .           " <br /> "
            . $row ['optional_list'] .      " <br /> "
            . $row ['information_only'] .   " <br /> "
            . $row ['chairperson'] .        " <br /> "
            . $row ['req_reviewers'] .      " <br /> "
            . $row ['review_duration'] .    " <br /> "
            . $row ['document_abstract'] .  " <br /> "; 


            echo "<br / >";

    }

But now when I run it I get this -

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /opt/htdocs/webpages/TC_Tool/Tool/searchByDocID.php on line 32

And line 32 is -

while ($result = mysql_fetch_array($searchById))

And when I changed it to how you suggested so it looked like this -

if ($_POST['searchID'])
{
    $doc_id = $_POST['searchID'];

    $searchById = "SELECT * FROM tc_tool.forms WHERE doc_id = '%$doc_id%'"; 
    $result = mysql_query($searchById);
    $row = mysql_fetch_assoc($result);


    if (!mysql_query($selectById, $connect_db))
        {
            echo "Query failed: $query<br />" . mysql_error() . " with query " . $searchById;
        }

        echo $row  ['doc_id'] .             " <br /> " 
            . $row ['doc_title'] .          " <br /> "
            . $row ['doc_number'] .         " <br /> "
            . $row ['doc_type'] .           " <br /> " 
            . $row ['revision'] .           " <br /> " 
            . $row ['cdm_link'] .           " <br /> "
            . $row ['mars_link'] .          " <br /> "
            . $row ['checklist_link'] .     " <br /> "
            . $row ['link_internal_1_3'].   " <br /> "
            . $row ['impacted_products'].   " <br /> "
            . $row ['scope'].               " <br /> "
            . $row ['impacted_products_2'] ." <br /> "
            . $row ['review_class'].        " <br /> "
            . $row ['full_simplified'] .    " <br /> "
            . $row ['pref_earliest'] .      " <br /> "
            . $row ['pref_latest'] .        " <br /> "
            . $row ['prev_reviews'] .       " <br /> "
            . $row ['proj_name'] .          " <br /> "
            . $row ['auth_name'] .          " <br /> "
            . $row ['req_list'] .           " <br /> "
            . $row ['optional_list'] .      " <br /> "
            . $row ['information_only'] .   " <br /> "
            . $row ['chairperson'] .        " <br /> "
            . $row ['req_reviewers'] .      " <br /> "
            . $row ['review_duration'] .    " <br /> "
            . $row ['document_abstract'] .  " <br /> "; 


            echo "<br / >";



            }

I get this error -

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /opt/htdocs/webpages/TC_Tool/Tool/searchByDocID.php on line 35
Query failed:
with query SELECT * FROM tc_tool.forms WHERE doc_id = '%Search By Doc ID%';

Any idea what else I can try?! I don't no if it something got to do with my query or what?...

[Biiim]

Hows this work?

    <?php
    ///-----------------------------
    /// Inserting data into database
    ///-----------------------------
    include 'connect_db.php';
    include 'newheader.php';
    function sanitize_data($data)
        {
            $data = array_map('trim',$data);
            $data = array_map('strip_tags',$data);
            $data = array_map('htmlspecialchars',$data);
            $data = array_map('mysql_real_escape_string',$data);
            return $data;
        }
        $post = sanitize_data($_POST);
    if ($_POST['searchID'])
    {
        $searchById = "SELECT * FROM tc_tool.forms WHERE doc_id = '$doc_id' LIMIT 1"; 
        $result = mysql_query($searchById);
        if($result !== false){
            $row = mysql_fetch_assoc($result);
            echo     $row  ['doc_id'] .             " <br /> "
                . $row ['doc_title'] .          " <br /> "
                . $row ['doc_number'] .         " <br /> "
                . $row ['doc_type'] .           " <br /> " 
                . $row ['revision'] .           " <br /> " 
                . $row ['cdm_link'] .           " <br /> "
                . $row ['mars_link'] .          " <br /> "
                . $row ['checklist_link'] .     " <br /> "
                . $row ['link_internal_1_3'].   " <br /> "
                . $row ['impacted_products'].   " <br /> "
                . $row ['scope'].               " <br /> "
                . $row ['impacted_products_2'] ." <br /> "
                . $row ['review_class'].        " <br /> "
                . $row ['full_simplified'] .    " <br /> "
                . $row ['pref_earliest'] .      " <br /> "
                . $row ['pref_latest'] .        " <br /> "
                . $row ['prev_reviews'] .       " <br /> "
                . $row ['proj_name'] .          " <br /> "
                . $row ['auth_name'] .          " <br /> "
                . $row ['req_list'] .           " <br /> "
                . $row ['optional_list'] .      " <br /> "
                . $row ['information_only'] .   " <br /> "
                . $row ['chairperson'] .        " <br /> "
                . $row ['req_reviewers'] .      " <br /> "
                . $row ['review_duration'] .    " <br /> "
                . $row ['document_abstract'] .  " <br /> ";
                echo "<br / >";   
        }else{
            echo "No results returned or Query failed: $searchById<br />" . mysql_error() . " <br /><br /> ";
        }
    }
    ?>
    <?php include "footer.php" ?>

Any more info on what data is in doc_id?

it seems like you are trying to do a like search in some places but are saying it's an id like 338 above

[fobos]

$search_id = $_POST['searchID'];
$searchById = "SELECT * FROM tc_tool.forms WHERE doc_id LIKE "%$search_id%"';

But i think the problem is with your table name because database and table names cannot contain “/”, “\”, “.”, or characters that are not permitted in file names.
So..

tc_tool**.forms**

is innapropriate, but

tc_tool

is ok to use.

[broj1]

You can not use % wildcards if you use equals (=) operator. Wildcards (% or _) can be used only with LIKE statement. I suggested you use equal operator only if you are certain that doc_id is an integer not a string (or a complete string to match not just part of). In that case the query would be:

$searchById = "SELECT * FROM tc_tool.forms WHERE doc_id = $doc_id"; // $doc_id is and integer

or

$searchById = "SELECT * FROM tc_tool.forms WHERE doc_id = '$doc_id'"; // $doc_id is a complete string

That was just a suggestion. If you want to use wildcards then stick to your version:

$searchById = "SELECT * FROM tc_tool.forms WHERE doc_id LIKE '%$doc_id%'";

[broj1]

Just to comment on the post by fobos above. If you use tc_tool.forms it is OK since this is mySql's notation of database_name.table_name or table_name.col_name. So tc_tools is probably a database name and forms is table name. Maybe tubesnube can confirm or correct me.

[tubesnube]

Sorry for the late reply, this is how it looks now but it doesnt return anything! Any ideas?

<?php

include 'connect_db.php';
include 'newheader.php';

function sanitize_data($data)
    {
        $data = array_map('trim',$data);
        $data = array_map('strip_tags',$data);
        $data = array_map('htmlspecialchars',$data);
        $data = array_map('mysql_real_escape_string',$data);
        return $data;
    }
    $post = sanitize_data($_POST);

if (isset($post['searchID']) && $post['searchbyequipmenttype'] != '')
{
$id = $post['searchbyequipmenttype'];
$sql = <<<SQL
SELECT *
FROM tc_tool.forms
WHERE
(
doc_id = $id
)
SQL;

$data = mysql_query($sql) or die(mysql_error()."<br>-----------<br>$sql");

    while ($result = mysql_fetch_array( $data ))
    {
        echo $row  ['doc_id'] .             " <br /> " 
            . $row ['doc_title'] .          " <br /> "
            . $row ['doc_number'] .         " <br /> "
            . $row ['doc_type'] .           " <br /> " 
            . $row ['revision'] .           " <br /> " 
            . $row ['cdm_link'] .           " <br /> "
            . $row ['mars_link'] .          " <br /> "
            . $row ['checklist_link'] .     " <br /> "
            . $row ['link_internal_1_3'].   " <br /> "
            . $row ['impacted_products'].   " <br /> "
            . $row ['scope'].               " <br /> "
            . $row ['impacted_products_2'] ." <br /> "
            . $row ['review_class'].        " <br /> "
            . $row ['full_simplified'] .    " <br /> "
            . $row ['pref_earliest'] .      " <br /> "
            . $row ['pref_latest'] .        " <br /> "
            . $row ['prev_reviews'] .       " <br /> "
            . $row ['proj_name'] .          " <br /> "
            . $row ['auth_name'] .          " <br /> "
            . $row ['req_list'] .           " <br /> "
            . $row ['optional_list'] .      " <br /> "
            . $row ['information_only'] .   " <br /> "
            . $row ['chairperson'] .        " <br /> "
            . $row ['req_reviewers'] .      " <br /> "
            . $row ['review_duration'] .    " <br /> "
            . $row ['document_abstract'] .  " <br /> "; 
    }
    }
    else{
  echo "There was a problem with the form, please try again and make sure you have filled in a document number";
  }
include "footer.php" ?>

As for whoever asked what doc_id is, it is my primary key in my database and whenever someone submits a form on my website the doc_id increments, so basically I want to be able to enter the doc_id eg. 300 into the search bar and it will return the form that is associated with the id that was entered into the search bar.

[broj1]

The code seems to be OK. It can be a problem in that the query is not what you expect. Maybe you have already done this but if not put the following statement on line 27 and post here what it outputs:

die($sql);

[Biiim]

$sql = <<<SQL
SELECT *
FROM tc_tool.forms
WHERE
(
doc_id = $id
)
SQL;

Whats that?

Try this:

 <?php
 include 'connect_db.php';
 include 'newheader.php';

 error_reporting(E_ALL);
 function sanitize_data($data)
 {
 $data = array_map('trim',$data);
 $data = array_map('strip_tags',$data);
 $data = array_map('htmlspecialchars',$data);
 $data = array_map('mysql_real_escape_string',$data);
 return $data;
 }
 var_dump($_POST);
 $post = sanitize_data($_POST);
 var_dump($post);
 if (isset($post['searchID']) && $post['searchbyequipmenttype'] != '')
 {
 $id = $post['searchbyequipmenttype'];
 //$id = 388;//test a known existing form
 $sql = "SELECT *
 FROM tc_tool.forms
 WHERE doc_id = {$id}";

 $RESULT = mysql_query($sql) or die(mysql_error()."<br>-----------<br>$sql");
 while ($row = mysql_fetch_array( $RESULT ))
 {
 echo $row ['doc_id'] . " <br /> "
 . $row ['doc_title'] . " <br /> "
 . $row ['doc_number'] . " <br /> "
 . $row ['doc_type'] . " <br /> "
 . $row ['revision'] . " <br /> "
 . $row ['cdm_link'] . " <br /> "
 . $row ['mars_link'] . " <br /> "
 . $row ['checklist_link'] . " <br /> "
 . $row ['link_internal_1_3']. " <br /> "
 . $row ['impacted_products']. " <br /> "
 . $row ['scope']. " <br /> "
 . $row ['impacted_products_2'] ." <br /> "
 . $row ['review_class']. " <br /> "
 . $row ['full_simplified'] . " <br /> "
 . $row ['pref_earliest'] . " <br /> "
 . $row ['pref_latest'] . " <br /> "
 . $row ['prev_reviews'] . " <br /> "
 . $row ['proj_name'] . " <br /> "
 . $row ['auth_name'] . " <br /> "
 . $row ['req_list'] . " <br /> "
 . $row ['optional_list'] . " <br /> "
 . $row ['information_only'] . " <br /> "
 . $row ['chairperson'] . " <br /> "
 . $row ['req_reviewers'] . " <br /> "
 . $row ['review_duration'] . " <br /> "
 . $row ['document_abstract'] . " <br /> ";
 }
 }
 else{
 echo "There was a problem with the form, please try again and make sure you have filled in a document number";
 }
 include "footer.php" ?>

[Biiim]

while ($result = mysql_fetch_array( $data ))

after reading through it the error is probably you define it $result = mysql_fetch_array then inside the loop refer to $row which doesnt exist

[broj1]

Biim is right. Reference was made correctly in your first post but was changed to wrong in your post 4 days ago.

[tubesnube]

Thanks Biiim, it works! :)