Processing the Search String with PHP and MySQL

Question

I'm in the process of making a web page that's meant to display data that's within a database. The database is stored in MySQL and I'm making the web page in PHP. The PHP code that I have is

<?php
    $query = "select * from project where ".$searchtype." like '%".$searchterm."%'";
    $result = mysqli_query($db,$query);
    $num_results = mysqli_num_rows($results);

    echo "<p>Number of projects found: ".$num_results."</p>";
    for ($i=0; $i <$num_results; $i++) {
        $row = mysqli_fetch_assoc($result);
        echo "<p><strong>".($i+1).". Title: ";
        echo htmlspecialchars(stripslashes($row['title']));
        echo "</strong><br />Author: ";
        echo stripslashes($row['author']);
        echo "<br />ISBN: ";
        echo stripslashes($row['isbn']);
        echo "<br />Price: ";
        echo stripslashes($row['price']);
        echo "</p>";
    }
    $mysqli_free_result($result);
    $mysqli_close($db);
?>

This PHP script is meant to load different projects that's within the database and in a welcome.php script that calls this script connects to the database and it does connect to it correctly. The problem that I'm having is when I run this script, is that I get the following:

Number of projects found:

As shown, it doesn't display any data from the database.

EDIT

My welcome.php script is

<?php
    $hostname='mysql.uniwebsite.ac.uk';
    $database='uniusername';
    $username='database';
    $password='password';

    $db= mysql_connect($hostname, $username, $password);
    if (!$link) {
        die('Connection failed: ' . mysql_error());
    }
    else{
         echo "Connection to MySQL server " .$hostname . " successful!
    " . PHP_EOL;
    }

    $db_selected = mysql_select_db($database, $link);
    if (!$db_selected) {
        die ('Can\'t select database: ' . mysql_error());
    }
    else {
        echo 'Database ' . $database . ' successfully selected!';
    }


?>

EDIT #2

My projects.php code is

<?php
    $searchtype=$_POST['searchtype'];
    $searchterm=trim($_POST['searchterm']);
    if (!$searchtype || !$searchterm) {
        echo 'No search details. Go back and try again.';
        exit;
    }

    $query = "select * from project where ".$searchtype." like '%".$searchterm."%'";
    var_dump($query);

    $result = mysqli_query($link,$query);
    $num_results = mysqli_num_rows($result);

    echo "<p>Number of projects found: ".$num_results."</p>";
    for ($i=0; $i <$num_results; $i++) {
        $row = mysqli_fetch_assoc($result);
        echo "<p><strong>".($i+1).". Project Number: ";
        echo htmlspecialchars(stripslashes($row['projectNo']));
        echo "</strong><br />Project Name: ";
        echo stripslashes($row['pjname']);
        echo "<br />Project City: ";
        echo stripslashes($row['city']);
        echo "</p>";
    }
    mysqli_free_result($result);
    mysqli_close($link);
?>

And when I run it, I get No search details. Go back and try again.

EDIT #3

In my projects.php I have now got

        <form action="list_projects.php" method="post">
        <p>Choose Search Type: <br /></p>
        <select name="searchtype">
            <option value="partNo">Part Number</option>
            <option value="pname">Part Name</option>
            <option value="color">Part Colour</option>
            <option value="weight">Part Weight</option>
            <option value="city">City</option>
        </select>
        <br />
        <p>Enter Search Term: </p>
        <br />
        <input name="searchterm" type="text" size="20"/>
        <br />
        <input type="submit" name="submit" value="Search"/>
    </form>

    <?php
        $searchtype=$_POST['searchtype'];
        $searchterm=trim($_POST['searchterm']);
        if (!$searchtype || !$searchterm) {
            echo 'No search details. Go back and try again.';
            exit;
        }

        $query = "select * from project where ".$searchtype." like '%".$searchterm."%'";
        var_dump($query);

        $result = mysqli_query($link,$query);
        $num_results = mysqli_num_rows($result);

        echo "<p>Number of projects found: ".$num_results."</p>";
        for ($i=0; $i <$num_results; $i++) {
            $row = mysqli_fetch_assoc($result);
            echo "<p><strong>".($i+1).". Part Number: ";
            echo htmlspecialchars(stripslashes($row['partNo']));
            echo "</strong><br />Part Name: ";
            echo stripslashes($row['pname']);
            echo "<br />Part Colour: ";
            echo stripslashes($row['color']);
            echo "<br />Part Weight: ";
            echo stripslashes($row['weight']);
            echo "<br />City";
            echo stripcslashes($row['city']);
            echo "</p>";
        }
        mysqli_free_result($result);
        mysqli_close($link);
?>

but when I run it, I get string(49) "select * from project where projectNo like '%J1%'" Number of projects found:

@Marcus I can connect to the database. As I get Connection to MySQL server mysql.uniwebsite.ac.uk successful! Database database successfully selected! — user3283410
– user3283410, Commented Apr 10, 2016 at 12:45
There are two mystery things in your comment: you are using $link variable for DB connection but then using $db and also closing the DB connection before calling queries on it ... yeah, the full script will help to understand the whole thing ... — Filip Matthew
– Filip Matthew, Commented Apr 10, 2016 at 12:58

Filip Matthew · Accepted Answer · 2016-04-10 13:14:28Z

1

EDIT

Here are the both files corrected. There were few more typos with function names like $mysqli_free_result($result) and $mysqli_close($db).

File welcome.php:

<?php
    $hostname='mysql.uniwebsite.ac.uk';
    $database='uniusername';
    $username='database';
    $password='password';

    $link = mysql_connect($hostname, $username, $password);
    if (!$link) {
        die('Connection failed: ' . mysql_error());
    }
    else{
         echo "Connection to MySQL server " .$hostname . " successful!
    " . PHP_EOL;
    }

    $db_selected = mysql_select_db($database, $link);
    if (!$db_selected) {
        die ('Can\'t select database: ' . mysql_error());
    }
    else {
        echo 'Database ' . $database . ' successfully selected!';
    }
?>

<?php
    $query = "select * from project where ".$searchtype." like '%".$searchterm."%'";
    // var_dump($query); -- uncomment to make sure the final query makes sense after filling those variables

    $result = mysqli_query($link,$query);
    $num_results = mysqli_num_rows($result);

    echo "<p>Number of projects found: ".$num_results."</p>";
    for ($i=0; $i <$num_results; $i++) {
        $row = mysqli_fetch_assoc($result);
        echo "<p><strong>".($i+1).". Title: ";
        echo htmlspecialchars(stripslashes($row['title']));
        echo "</strong><br />Author: ";
        echo stripslashes($row['author']);
        echo "<br />ISBN: ";
        echo stripslashes($row['isbn']);
        echo "<br />Price: ";
        echo stripslashes($row['price']);
        echo "</p>";
    }
    mysqli_free_result($result);
    mysqli_close($link);
?>

edited Apr 10, 2016 at 13:14

answered Apr 10, 2016 at 12:46

Filip Matthew

3282 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

user3283410 Over a year ago

I've changed it so it's like that, but I'm still not getting an output

Filip Matthew Over a year ago

Well, also try to var_dump($query) and make sure the SQL query (after evaluating your variables $searchtype and $searchterm is syntactically correct. It is possible that there is error in your SQL syntax, the database response with an error and then mysqli_num_rows would result with a null.

Filip Matthew Over a year ago

I updated my answer. Now the same connection variable is used everywhere, the results->result is corrected and two typos with function names at the bottom. Also make sure the final query makes sense before sending to the database (uncomment the var_dump)

mferly Over a year ago

$num_results = mysqli_num_rows($results); still hasn't been corrected.

mferly Over a year ago

OP give the above a try. And uncomment the var_dump($query); and post the output here, as well, just to be certain you're even passing a valid statement to the query.

|

mferly · Accepted Answer · 2016-04-10 13:02:58Z

0

UPDATE

Remove mysql_close($link);

And then, instead of using $db in the rest of your code, use $link.

$result = mysqli_query($link, $query);

edited Apr 10, 2016 at 13:02

answered Apr 10, 2016 at 12:45

mferly

1,6561 gold badge13 silver badges19 bronze badges

1 Comment

user3283410 Over a year ago

I've changed it, but I'm still not getting an output

Collectives™ on Stack Overflow

Processing the Search String with PHP and MySQL

2 Answers 2

16 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related