Making a PHP/MySQL Search Engine

Question

In a site I'm making, I need a search engine to find songs for people to listen to. I have it working to the point that it can get info from the database and display them on the page. The problem comes when there are two songs with the same name. I have a system so the results will go to separate links, but when I search them they display the same image even though there is two separate sources for them. It also will make extra results for some reason. Here's my code:

<?php
if (isset($_GET['q'])) {
    $q = $_GET['q'];
    mysql_connect('********', '********', '********');
    mysql_select_db('********');
    $query = mysql_query("SELECT * FROM ******** WHERE title LIKE '$q'");
    $numrows = mysql_num_rows($query);
} else {
    echo "
<span style='font-family: Helvetica, Arial;font-weight: bold;font-size: 25px;'>Search</span>
<form action='http://www.example.com/search' method='get'>
<input placeholder='Search for music' type='text' name='q' style='font-weight:bold;padding:5px;width:300px;border-top-left-radius: 4px;border-top-right-radius: 10px;border-bottom-left-radius: 10px;border-bottom-right-radius: 4px;border: 3px solid gray;background-color:#000000;color:#FFFFFF;' />
</form>
    ";
}
if ($numrows != 0) {
    $index = 0;
    $results = array();
    while($row = mysql_fetch_assoc($query)) {
        $results[$index] = $row;
        $index++;
        foreach ($results as $result) {
            $url = "http://www.example.com?id=" . $row['id'];
            $title = $row['title'];
            $arturl = $row['art_url'];
            if ($_GET['q'] != "") {
                echo "
                    <a href='$url'>
                    <table>
                    <tr style='text-align:left;'>
                    <td><img src='$arturl' style='width:100px;height:100px;'></td>
                    <td>
                    <span class='songTitle'>$title</span>
                    <br/>
                    <span class='songArtist'>By: Unknown</span>
                    </td>
                    </tr>
                    </table>
                    </a>
                    <br />              
                ";
            }
        }   
    }
} else {
    if ($_GET['q'] != "") {
        echo "
<span style='font-family: Helvetica, Arial;font-weight: bold;font-size: 25px;'>Search</span>
<form action='********' method='get'>
<input placeholder='Search for music' type='text' name='q' style='font-weight:bold;padding:5px;width:300px;border-top-left-radius: 4px;border-top-right-radius: 10px;border-bottom-left-radius: 10px;border-bottom-right-radius: 4px;border: 3px solid gray;background-color:#000000;color:#FFFFFF;' />
</form>
        ";
        echo "<br />No results where found.";
    }
}
?>

You definitely won't have a very good search engine if you're using `mysql_` functions. They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, Jan 18 '13 at 01:57
Okay, I'll read that. But for now I need this to work before I completely change the site! — y--, Jan 18 '13 at 02:00
I don't think you understand the consequences of using the code you have. — Kermit, Jan 18 '13 at 02:01
^^^^as true as it is, its getting a little tedious for regular S.O users to see it 10 times a day. — , Jan 18 '13 at 02:04
I am currently looking into MySQLi right now guys! I just need a solution for now. — y--, Jan 18 '13 at 02:07
@Dagon They probably won't see it if they actually appreciated what the message says. — Kermit, Jan 18 '13 at 02:09
@Gordan: "*Before I completely change the site*" - inserting the letter `i` into each `mysql_` function call (which is almost all that is *necessary* to switch to MySQLi) is hardly a massive deal. One should, of course, take the opportunity to parameterise statements too - but that's another matter. — eggyal, Jan 18 '13 at 02:10
Really?! Wow. I guess I'll try that then. And what do you mean by "parameterise statements"? (Sorry I'm some-what new to MySQL) — y--, Jan 18 '13 at 02:26

Phphelp · Accepted Answer · 2013-01-18T02:59:59.303

2

while($row = mysql_fetch_array($result))Try $result['title'] instead of $row['title'];. Same goes to $url and $arturl

This should work.

if ($numrows != 0) {
    $index = 0;
    $results = array();
    while($row = mysql_fetch_array($query)) {
            $url = "http://www.example.com?id=" . $row['id'];
            $title = $row['title'];
            $arturl = $row['art_url'];
            if ($_GET['q'] != "") {
                echo "
                    <a href='$url'>
                    <table>
                    <tr style='text-align:left;'>
                    <td><img src='$arturl' style='width:100px;height:100px;'></td>
                    <td>
                    <span class='songTitle'>$title</span>
                    <br/>
                    <span class='songArtist'>By: Unknown</span>
                    </td>
                    </tr>
                    </table>
                    </a>
                    <br />              
                ";
            }
    }           
    }

edited Jan 18 '13 at 02:59

answered Jan 18 '13 at 02:19

Phphelp

1,290
2
14
25

It's displaying the info just fine. The problem is, it's making duplicates. – y-- Jan 18 '13 at 02:24
i think you should use either 'while' or 'foreach'. Not both. There is no need of that while loop. – Phphelp Jan 18 '13 at 02:25
Is it bad that I use both? What is the reason? Also, how do I change that `foreach` loop to a `while` loop? – y-- Jan 18 '13 at 02:28
Okay. I did what you said, removed the `while` loop and change `$row[''];` to `$result[''];`. Now it's just displaying one result. There is supposed to be two. – y-- Jan 18 '13 at 02:34
that is because of your index++. Remove it. – Phphelp Jan 18 '13 at 02:39
Also echo $numrows and make sure there are 2 results. – Phphelp Jan 18 '13 at 02:46
WHOA! That didn't work!! It was displaying infinite results.. WTF? – y-- Jan 18 '13 at 02:48
http://www.dione.site11.com/search/?q=Song+title Okay, maybe not INFINITE results but a lot of them. – y-- Jan 18 '13 at 02:49
just echo $numrows and tell me what you are getting. That will match your result. – Phphelp Jan 18 '13 at 02:52
Don't know what the heck is goin' on there. It's not displaying the images at all now. It's making to of each of those "results". AND it's giving them weird names. Dangit. Okay I'll echo $numrows out... – y-- Jan 18 '13 at 02:53
try print_r($row) and see the result; – Phphelp Jan 18 '13 at 02:56
Array ( [0] => null [song_url] => null [1] => 5 [id] => 5 [2] => Song title [title] => Song title [3] => http://www.dione.site11.com/create/uploads/albumart_l9XwbY8E4SKqu7GR6NLreaQmnUhcIH3OxA1JZPvV_gun-100x100.jpg [art_url] => http://www.dione.site11.com/create/uploads/albumart_l9XwbY8E4SKqu7GR6NLreaQmnUhcIH3OxA1JZPvV_gun-100x100.jpg ) – y-- Jan 18 '13 at 02:59
there seems to be only one result when you tried print_r($row) – Phphelp Jan 18 '13 at 03:04
Okay. So after all of this I notice that the images are the same in the DB. I'm so stupid. I'm gonna change the image and try it. – y-- Jan 18 '13 at 03:15
http://www.dione.site11.com/search/?q=Song+title Okay It's working perfectly. Thank you so much for all your help! – y-- Jan 18 '13 at 03:16

score 0 · Answer 2 · answered Jan 18 '13 at 02:02

0

your codes seem right. However, to clarify, try to echo $arturl and see if it is getting the right source name. And also check the value in the database. Last thing is try to check whether the source image is in the correct folder. Try and give the feedback.

answered Jan 18 '13 at 02:02

Bizarre

106
2
12

You can see what I'm talking about here: http://www.dione.site11.com/search/?q=Song+title – y-- Jan 18 '13 at 02:12
i have seen it. the first one is id=5, but the second and third id=8. the id is same. better check that one. the id should not be the same. try to remove foreach, and just use while loop and see what is going on. while($row = mysql_fetch_assoc($query)) { echo $row['id']. " " . $row['titlle'] . " " .$row['art_url']; } – Bizarre Jan 18 '13 at 02:31
Check it out now. I did what @Phphelp said to do. Still having problems though. (Now it's the opposite. There is only one result.) – y-- Jan 18 '13 at 02:36
try the current post by Phphelp, I can't post a code here but i will go for phphelp codes. – Bizarre Jan 18 '13 at 02:48
hey try this if ($numrows != 0) { while($row=mysql_fetch_array($query)){ echo $row['id']. " " . $row['titlle'] . " " .$row['art_url']; } } try this simple code first. remove those indexes,array,foreach. just use while loop. – Bizarre Jan 18 '13 at 02:53
give it a try and see what happen. – Bizarre Jan 18 '13 at 02:54

Making a PHP/MySQL Search Engine

2 Answers2