My SQL Echo Matching Query(s)

Question

Hi I am trying to print all matching rows where the matching Album or Artist is matched. I have the code here but something is wrong. I've tried researching it but cant find anything distinct. I think that I am on the right track.

This isn't a duplicate question. Its not the quotes that are the problem because even when I removed them and changed the name it still does not work.

<form>
<h3>Please either search by Artist or Album:</h3>
    Artist: <input type="text" name="artist"><br /><br />
    Album: <input type="text" name="album"><br /><br />
    <input type="submit" value="Search Database" name="submit" />
</form>
<?php
    if(isset($_POST["submit"])){
        $con=mysql_connect('localhost','root','password') or die(mysql_error());    
        mysql_select_db('music') or die("cannot select DB");

        if(!empty($_POST['artist'])) {
            $name = $_POST['artist'];
            $sql = "SELECT * FROM 'artists' WHERE 'artists name' =".$name;
            $result = mysql_query($sql);
            while($row = mysql_fetch_array($result)) {
                echo $row['Album'] . ':' . $row['Artist Name'] . '<br />';
            }
        } else if(!empty($_POST['album'])) {
            $name = $_POST['album'];
            $sql = "SELECT * FROM 'albums' WHERE 'Album' =".$name;
            $result = mysql_query($sql);
            while($row = mysql_fetch_array($result)) {
                echo $row['Artists'] . ':' . $row['Album'] . '<br />';
            }
        } else {
            echo "<p>Please ente a search query</p>";
        }
    }
?>

Thanks!

Use backticks to quote table and column names, not single quotes. Or don't quote them at all -- you only have to quote names if they contain special characters or are reserved words. — Barmar, Apr 12 '15 at 02:30
@th3r1singking also is 'artists name' a valid column name? it contains a space — Arzgethalm, Apr 12 '15 at 02:32
@Arzgethalm yes it is a valid column name, should i remove the space? — th3r1singking, Apr 12 '15 at 02:33
you will encounter some problems with it later on. so you can replace that with underscore _ — Arzgethalm, Apr 12 '15 at 02:35
@Arzgethalm You can use any character you want in a column name, you just have to quote it with backticks. — Barmar, Apr 12 '15 at 02:41

Barmar · Answer 1 · 2015-04-13T18:17:18.683

Use backticks, not quotes, to escape table and column names that contain special characters. Quotes are for making literal strings.

Another problem is that the strings you're comparing to in the WHERE clause need to be put in quotes. And you need to use mysql_real_escape_string to prevent SQL injection (it would be better if you upgraded to PDO or mysqli and used prepared statements).

You need to use <form method="post"> so that $_POST will be filled in with the form fields, or use $_GET to get the form fields. You could instead use $_REQUEST, which will work with either type of form.

<?php
    if(isset($_POST["submit"])){
        $con=mysql_connect('localhost','root','password') or die(mysql_error());    
        mysql_select_db('music') or die("cannot select DB");

        if(!empty($_POST['artist'])) {
            $name = $_POST['artist'];
            $sql = "SELECT * FROM `artists` WHERE `artist name` = '".mysql_real_escape_string($name)."'";
            $result = mysql_query($sql) or die(mysql_error());
            while($row = mysql_fetch_array($result)) {
                echo $row['Album'] . ':' . $row['Artist Name'] . '<br />';
            }
        } else if(!empty($_POST['album'])) {
            $name = $_POST['album'];
            $sql = "SELECT * FROM `albums` WHERE `Album` = '".mysql_real_escape_string($name)."'";
            $result = mysql_query($sql) or die(mysql_error());
            while($row = mysql_fetch_array($result)) {
                echo $row['Artists'] . ':' . $row['Album'] . '<br />';
            }
        } else {
            echo "<p>Please ente a search query</p>";
        }
    }
?>

I tried that and it did not resolve the issue. It's not a quotes or names issue — th3r1singking, Apr 12 '15 at 02:51
In your `WHERE` clause you have the column name `artists name`, but you print `$row['Artist Name']`. Is there an `s` at the end of `Artist` or not? Also, make sure you get the capitalization correct, PHP is case-sensitive. — Barmar, Apr 12 '15 at 03:07
See my additional change. You didn't quote the names that you were comparing with. — Barmar, Apr 13 '15 at 01:59

My SQL Echo Matching Query(s)

1 Answers1