View Full Version : Help with PHP/MySQL query

Jon M
12-02-2011, 11:04 PM
Hello coding legends,

Hopefully you can help me… I am trying to get my head around PHP and MySQL… I’m a bit of a beginner at all this.

I have constructed a MySQL database of musical artists who have achieved number one positions since records began in the UK.

The data is formatted as follows (quick example)

artist : Title : date : month : year
Rihanna featuring Calvin Harris : We Found Love : 18 : November : 2011

What I am hoping to do is have some code that can search my database when I submit a specific date (set in three parts day:month:year). I am aiming for the code to then bring back what artist/title of song was at number one on the queried date.

I seem to have ‘semi got there’ however when I select a date, it just brings every entry on the entire database back. It doesn’t filter it down to just the specific date I’m querying.

What I have done wrong/missed out/messed up? Your thoughts and help would be very much appreciated.



Code as follows…


if (!isset($_POST['Submit'])) {
// form not submitted

<form action="<?=$_SERVER['php_SELF']?>" method="post">

<select size="1" name="date">
<option value="" selected>Date...</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>


<select size="1" name="month">
<option value="" selected>Month...</option>
<option value="January">January</option>
<option value="February">February</option>
<option value="March">March</option>
<option value="April">April</option>
<option value="June">June</option>
<option value="July">July</option>
<option value="August">August</option>
<option value="September">September</option>
<option value="October">October</option>
<option value="November">November</option>
<option value="December">December</option>

<select size="1" name="year">
<option value="" selected>Year...</option>
<option value="1953">1953</option>
<option value="1954">1954</option>
<option value="1955">1955</option>
<option value="1956">1956</option>
<option value="1957">1957</option>
<option value="1958">1958</option>
<option value="1959">1959</option>
<option value="1960">1960</option>
<option value="1961">1961</option>
<option value="1962">1962</option>
<option value="1963">1963</option>
<option value="1964">1964</option>
<option value="1965">1965</option>
<option value="1966">1966</option>
<option value="1967">1967</option>
<option value="1968">1968</option>
<option value="1969">1969</option>
<option value="1970">1970</option>
<option value="1971">1971</option>
<option value="1972">1972</option>
<option value="1973">1973</option>
<option value="1974">1974</option>
<option value="1975">1975</option>
<option value="1976">1976</option>
<option value="1977">1977</option>
<option value="1978">1978</option>
<option value="1979">1979</option>
<option value="1980">1980</option>
<option value="1981">1981</option>
<option value="1982">1982</option>
<option value="1983">1983</option>
<option value="1984">1984</option>
<option value="1985">1985</option>
<option value="1986">1986</option>
<option value="1987">1987</option>
<option value="1988">1988</option>
<option value="1989">1989</option>
<option value="1990">1990</option>
<option value="1991">1991</option>
<option value="1992">1992</option>
<option value="1993">1993</option>
<option value="1994">1994</option>
<option value="1995">1995</option>
<option value="1996">1996</option>
<option value="1997">1997</option>
<option value="1998">1998</option>
<option value="1999">1999</option>
<option value="2000">2000</option>
<option value="2001">2001</option>
<option value="2002">2002</option>
<option value="2003">2003</option>
<option value="2004">2004</option>
<option value="2005">2005</option>
<option value="2006">2006</option>
<option value="2007">2007</option>
<option value="2008">2008</option>
<option value="2009">2009</option>
<option value="2010">2010</option>
<option value="2011">2011</option>


<input type="Submit" value="Submit" name="Submit">


else {

// Server Variables
$host = "??";
$user = "??";
$pass = "??";
$db = "??";

$date = empty($_POST['date'])? die ("ERROR: Select date from dropdown") : mysql_escape_string($_POST['date']);
$month = empty($_POST['month'])? die ("ERROR: Select month from dropdown") : mysql_escape_string($_POST['month']);
$year = empty($_POST['year'])? die ("ERROR: Select year from dropdown") : mysql_escape_string($_POST['year']);

// Open Connection

$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//Select Database

mysql_select_db($db) or die ("Unable to connect to database");

//Create query

$query = "SELECT * FROM Top40 WHERE '$date $month $year=$search'" or die (mysql_error());

$result = mysql_query($query) or die (mysql_error());



echo "<b><center>Database Output</center></b><br><br>";

while ($i < $num) {


echo "<b>$date $month $year</b><br>Artist: $artist<br><br>Title: $title Rows\n<br><hr><br>";




12-02-2011, 11:30 PM
$query = "SELECT * FROM Top40 WHERE '$date $month $year=$search'" or die (mysql_error());

Is what you have wrong. Since each field is seperated, you'll have to do something like:

$query = "SELECT * FROM Top40 Where `date` = '$date' AND `month` = '$month' AND `year` = '$year'"

And I have no clue where the variable $search came from. hehe

Jon M
12-03-2011, 09:25 AM
Can I just say a MASSIVE thank you to you! Works an absolute treat!

Very much appreciated!! :thumbsup::)