I'm getting no errors, but also getting no results - mysql php db

Question

I'm trying to grab a user submission from a database with two columns. One for Artist and one for title. I want to take their input from the simple form and output all of the similar results into a table on the next page. I've included the entire script I've written thus far. I'm not getting any errors on the page, but I'm also not getting any results. I've spent several days looking online to see if I can clear this up on my own, but I've had no such luck. Sorry for being so wordy, but I'm new to this site and wanted to provide as much detail as possible.

<?php 
include("db_connect.php"); 
// - get form data from "searchform.php"
$song_query = $_GET['song_query'];
// - column 1 and column 2 are all we're looking for in the db 
// - title and artist are currently the two cols. Table is named 
"song_list"
$col1 = "title";
$col2 = "artist";
$tablename = "song_list";
echo "<H1>Search results</H1>";
if ($song_query == "") {
echo "What are you going to sing? You didn't enter a search term! Please 
try again.";
exit;
}
// - pick the db connection out of the included file and give error if 
failed.
mysqli_select_db($conn, $db_name) or die(mysqli_error());
// - cleans up string inputted by user to avoid harmful code insertion 
into form
$song_query = strtoupper($song_query);
$song_query = strip_tags($song_query);
$song_query = trim($song_query);
// - set up parameters for accessing the query of the db
$sql = "SELECT $col1, $col2 FROM $tablename WHERE $col1, $col2 LIKE 
'%$song_query%'";
$result = mysqli_query($conn, $sql);
if (isset($_GET['$result'])){
if (mysqli_num_rows($result) > 0){
    echo "<table><tr>";
    echo "<th>Artist</th>";
    echo "</tr>";

while($row = mysqli_fetch_array($result)){
    echo "<tr>";
    echo "<td>" . $row['$result'] . "</td>";
    echo "</tr>";
    echo "</table>";
    }
    }
}
?>

What do you expect `WHERE $col1, $col2 LIKE '%$song_query%'";` to do? — PM 77-1, Dec 22 '17 at 15:35
MySQLi tends to fail silently. Always check for [mysqli errors]( http://php.net/manual/en/mysqli.error.php) to make sure your database calls work correctly. — aynber, Dec 22 '17 at 15:40
I apologize for being such an amateur, I'm doing this as a learning project to teach myself something other than graphic design. I'm piecing together several tutorials to develop this code. The isset got rid of my last error and w3c gave me the idea to use WHERE and LIKE with the name of the two columns. — Isaac, Dec 22 '17 at 15:45
Thank you all for your input! I'm still really new to this and I'm grateful for all the help I've gotten. I had to completely rewrite this thing like five times, but I finally got it working. It was a great learning experience. Thank you! -- I used a little bit of everyone's advice to get it working and I commented the crap out of it. lol — Isaac, Dec 26 '17 at 13:46

score 0 · Accepted Answer · answered Dec 22 '17 at 15:39

You have wrong SQL, which is getting constructed at runtime

$sql = "SELECT $col1, $col2 FROM $tablename WHERE $col1, $col2 LIKE 
'%$song_query%'";

which becomes

$sql = "SELECT title, artist FROM $tablename WHERE title, artist LIKE 
'%$song_query%'";

Look at WHERE title, artist LIKE here

$song_query gets value from $_GET['song_query'],which changes at runtime.

RiggsFolly · Answer 2 · 2017-12-23T16:13:06.230

0

This WHERE $col1, $col2 LIKE '%$song_query%' is invalid syntax you need to say

WHERE col1 LIKE '%something%' AND col2 LIKE '%something%'

So this should fix the problem

$sql = "SELECT $col1, $col2 
        FROM $tablename 
        WHERE $col1 LIKE '%$song_query%'
        AND $col2 LIKE '%$song_query%'";

Although this is wide open to SQL Injection Attack Even if you are escaping inputs, its not safe! Use prepared parameterized statements

$sql = "SELECT title, artist 
        FROM songlist 
        WHERE title LIKE ? AND artist LIKE ?";

$stmt = $conn->prepare($sql);
$val = sprintf('%%%s%%', $song_query);
$stmt->bind_param('ss',$val, $val);

$stmt->execute();

$stmt->bind_result($title, $artist);

echo "<table><thead><tr>";
echo "<th>Artist</th><td>Title</th>";
echo "</tr></thead><tbody>";

while ($stmt->fetch()) {
    echo "<tr>";
        echo "<td>$artist</td>";
        echo "<td>$title</td>";
    echo "</tr>";
}
echo "</tbody></table>";

Also note you made a couple of mistakes building your table that I think I have fixed.

edited Dec 23 '17 at 16:13

answered Dec 22 '17 at 16:01

RiggsFolly

83,545
20
96
136

Thank you RiggsFolly! I'll be using this on a local server at my karaoke shows, so I'm not really too worried about an injection attack. There won't be any internet access on the system hosting this. – Isaac Dec 22 '17 at 16:10
At least now I'm getting an error with the code snippet you provided. lol...Warning: sprintf(): Too few arguments in C:\xampp\htdocs\bluesky\runit.php on line 36 Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in C:\xampp\htdocs\bluesky\runit.php:37 Stack trace: #0 {main} thrown in C:\xampp\htdocs\bluesky\runit.php on line 37 – Isaac Dec 23 '17 at 15:48
Ahh yes. My mistake. See amended `sprintf()` – RiggsFolly Dec 23 '17 at 16:13
It might be simpler to do `$val = "%$song_query%";` – RiggsFolly Dec 23 '17 at 16:14

I'm getting no errors, but also getting no results - mysql php db

2 Answers2