string（49）“select * from php mysql error

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

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

Answer 1

Because it's painful, I want to rewrite your code and show you how you should be doing this:

Please note that at the top of your page is a reference to an include file in which you would set your database variable ($link).

<?php
//include "../../reference/to/mysql/login.php";
/***
 * The below code block should be in your include file referenced above 
 ***/
$link = mysqli_connect("localhost", "my_user", "my_password", "my_db");
if (!$link) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}
/***
 * End connection block
 ***/

/***
 * Your data is POSTed so it can not be trusted and must at the
 * very least be escaped using the below functions.
 ***/
$searchtype=mysqli_real_escape_String($link,$_POST['searchtype']);
$searchterm=mysqli_real_escape_String($link,$_POST['searchterm']);
$searchterm=trim($searchterm);
/***
 * Because your $searchtype is a column reference you need to ensure
 * it fits the allowed characters criteria for MySQL columns
 ***/
$searchtype = preg_replace("/[a-z0-9_]/i","",$searchtype);

Please read the MySQL manual about the allowed characters to use in column names. $ is also allowed but I'm removing that from here because you really should not be using that symbol as a column name character.

if (!$searchtype || !$searchterm) {
    echo 'No search details. Go back and try again.';
    exit;
}

$query = "select * FROM project WHERE ".$searchtype." LIKE '%".$searchterm."%'";
$result = mysqli_query($link,$query)  or die("Line ".__LINE__." Error: ".mysqli_error($link));
$num_results = mysqli_num_rows($result);

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

Hopefully you can see here that I have replaced your for loop with a while loop that does the same thing, taking each row from the database one at a time and outputting it as an array with identifier $row .

I have also used mysqli_fetch_array instead of your fetch_assoc.

I have corrected the spelling mistake in your stripslashes function, but also replaced stripslashes with htmlspecialchars because stripslashes is an old and almost useless renegade function that should not be used with even remotely modern Database interfacing

Your issue is also that this page coded here has not had $link declared for it, the $link idenitifier needs to be set at the top of every page that wants to connect to the database. You need to remember that PHP does not remember standard variables across pages so just because you setup $link in welcome.php does NOT mean that it is known in this page here.

• Use or die (mysqli_error($link)); appended to the end of your queries to feedback to you what errors occur.
• You must also get into the habit of using PHP Error Reporting to make any headway in solving your own issues.
• $link is usually set up in a PHP include file that you simply call at the top of every PHP page that requires it.
• IF needed, details about how to connect to MySQLi.