Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    5
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Help with PHP/MySQL query

    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.

    Thanks,

    J!

    Code as follows…

    PHP Code:
    <?php

    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> 

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

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


    </select> 

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

    <?php 


    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()); 

    $num=mysql_numrows($result); 

    mysql_close($connect); 

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

    $i=0
    while (
    $i $num) { 

    $date=mysql_result($result,$i,"date"); 
    $month=mysql_result($result,$i,"month"); 
    $year=mysql_result($result,$i,"year"); 
    $artist=mysql_result($result,$i,"artist"); 
    $title=mysql_result($result,$i,"title"); 

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

    $i++; 



    ?> 

    </body> 
    </html>
    Last edited by Jon M; 12-02-2011 at 11:09 PM.

  • #2
    Regular Coder
    Join Date
    Oct 2009
    Location
    United States
    Posts
    158
    Thanks
    8
    Thanked 4 Times in 4 Posts
    PHP Code:
    $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:

    PHP Code:
    $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
    Adobe Dreamweaver shall be destroyed!

  • Users who have thanked votter for this post:

    Jon M (12-03-2011)

  • #3
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    5
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Can I just say a MASSIVE thank you to you! Works an absolute treat!

    Very much appreciated!!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •