Forum Moderators: coopster
I have the basic html file which has the details I want to search the database (createdb1) with tables for artist, cd and type (for type of music-checkbox)
I've started the php file but as I can't figure it out and have gone down endless dead-ends; I'm desperate to be pointed in the right direction! I've been trying for days on end but can't see the wood for the trees...! any help gratefully received!
I'm not sure how to set up the queries for the database, how to pull queries from the tables within the database, how to set up queries for the prices and year of release..... sorry; I don't know a lot about it :(
THE PHP BIT:
MY CONNECTION TO CD DATABASE (createdb1:
<?
<?php // Make a MySQL Connection
mysql_connect("localhost", "", "") or die(mysql_error());
mysql_select_db("createdb1") or die(mysql_error());
?>
<?
//error message (not found message)begins
$XX = "No Record Found, to search again please close this window";
//query details table begins
$query = mysql_query("SELECT * FROM
//i want customer to be able to search by a) type of music and/or b) artist and/or c) year of release and/or d)price - & show results in table
?>
THE HTML FORM:
<html> searchCD.htm
<form action="checkresult.php" method="post">
<p>Choose CD from:</p>
<p>TYPE of Music</p>
<p>C&W
<input type="checkbox" value="1" name="type[]" />
R&B
<input type="checkbox" value="2" name="type[]" />
pop
<input type="checkbox" value="3" name="type[]" />
blues
<input type="checkbox" value="4" name="type[]" />
</p>
<p>AND/OR</p>
<p>ARTIST
<select name="select" size="1">
<option>Bush</option>
<option>billie holiday</option>
<option>abba</option>
<option>dolly parton</option>
</select>
</p>
<p>AND/OR</p>
<p>Year of Release 1990 </p>
<p>AND/OR</p>
<p>Price:
<input type="text" name="textfield">
</p>
<p>
<input type="submit" name="submit" />
</p>
</form>
We have some threads in our library that will get you started with this, and then we can fine-tune it once you understand the basics. Here's a good start: The Basics of Extracting Data from MySQL [webmasterworld.com] and Developing a MySQL Search Query [webmasterworld.com]. The latter may be of interest to you :)
Hope this gets you on the right path!
Best of luck!
The form: (to select year of cd release)
<form action="TCTest4.php" method="post">
1999<input type="checkbox" value="1999" name="releaseyear[]" />
2005<input type="checkbox" value="2005" name="releaseyear[]" />
2003<input type="checkbox" value="2003" name="releaseyear[]" />
<input type="submit" />
</form>
{i have also tried using drop downs to try to extract info + same result = zero!)
<form method = "post" action = "TCTest2.php">
<p>Select a field to display:
<select name = "select">
<option selected = "selected">*</option>
<option>title</option>
<option>releaseyear</option>
<option>price</option>
<option>itemsinstock</option>
</select>
</p>
PHP bit:
<?php
$link = mysql_connect("localhost","","") or die("Unable to connect to SQL server");
mysql_select_db("CDdb1", $link) or die("Unable to select database");
$selectSql = "SELECT * FROM music WHERE $releaseyear = $_POST['releaseyear']";
$result = mysql_query($selectSql, $link);
echo '<pre>';
print_r($releaseyear);
echo '</pre>';
#
?>
Where I'm having the most struggles are with using variables/names for checkboxes - i.e. should I assign $releaseyear or should it be something like $checkbox - totally baffled!
OM
Later on I (hopefully) have another form on the page that will let them choose more than 1 - it is a form where they can choose an artist - but they can select more than one artist (e.g. where 2 or more have worked together on an album).
thanks
Ok, so then you don't want to base the search on more than one year ;)
Let's see. Instead of checkboxes, let's try radios:
<form action="TCTest4.php" method="post">
1999<input type="radio" value="1999" name="releaseyear" /><br/>
2005<input type="radio" value="2005" name="releaseyear" /><br/>
2003<input type="radio" value="2003" name="releaseyear" /><br/>
<input type="submit" />
</form>
And here's the PHP:
<?php
$link = mysql_connect("localhost","username","password");
mysql_select_db("CDdb1", $link) or die("Unable to select database");
#
$selectSql = "SELECT * FROM music WHERE $releaseyear = '".[url=http://www.php.net/mysql-real-escape-string]mysql_real_escape_string[/url]($_POST['releaseyear'])."'";
$result = mysql_query($selectSql, $link) or [url=http://www.php.net/die]die[/url]([url=http://www.php.net/mysql-error]mysql_error[/url]());
while($row = [url=http://www.php.net/mysql-fetch-assoc]mysql_fetch_assoc[/url]($result)) {
echo '<pre>';
print_r($row);
echo '</pre>';
}
[url=http://www.php.net/mysql-close]mysql_close[/url]($link);
?>
Try this to see what you get :)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= '2003'' at line 1
?
Change this:
$selectSql = "SELECT * FROM music WHERE $releaseyear = '".mysql_real_escape_string($_POST['releaseyear'])."'";
To this:
$selectSql = "SELECT * FROM music WHERE releaseyear = '".mysql_real_escape_string($_POST['releaseyear'])."'";
Are you ready for more of my queries? ;) ?
Just in case you're bored; ehem!
I have 2 more things I am trying to do on the page. 1) was my attempt at doing the checkbox to select the artist - which was going horribly wrong - it was my original version for the year listing - again got lost with assigning variables/names.
the second & final part is to let the user choose a cd within a price range. I was just going to give them 3 options (maybe I've just answered my own question here; if I use your last suggestion) with 3 ranges; 1 - 4.99, 5.00 - 9.99 etc etc.....
What I am hoping to achieve with the page is let the user see the list of cds available (I'm not php savvy enough to do a search box) and from the listing choose; a) the year of release b) choose artist c) choose price range.
My final query (!) is can I just use 1 form to attach all these selections or whats the best way to whittle down the queries?
Sorry again; but I have spent weeks trying to figure this out & getting frown lines & grey hair during this process! Help me keep my looks & sanity. :)
I'd like you to try this yourself before someone here does it for you; this is the only way you are going to learn ;) When you get something to work with, start a new thread and we can go from there. Refer to the links in my first post [webmasterworld.com] and use them as a guideline. Also, if you need query help [mysql.com...] or the Database Forum [webmasterworld.com] are good places to look for answers.
Good luck!
Thanks & take your point about putting in the work ;)
At the moment; I am re-jigging the code; so that I have 3 drop down lists + checkbox. I am getting there sloooowly but hopefully when/if (!) I get stuck I will get back here with the code & whr the problems are - this forum is brilliant; so far I am moving a lot quicker than working through the textbooks
Many thanks!
1. Not sure how to code it so that user can query from just 1 or all of the dropdowns (at the moment unless they select the correct answers from each dropdown - no results!) = defeats the whole purpose of searching.
2. Also, not sure how to code it so that checkbox option can also be used to search the catalogue.
[idea of page is that user can search catalogue by selecting year of release, artist name, music type & resulsts can be returned)
THE FORM:
<?php
$link = mysql_connect("localhost","","");
mysql_select_db("createdb1", $link) or die("Unable to select database");
#
$selectSql1 = "SELECT * FROM music WHERE releaseyear = '".mysql_real_escape_string($_POST['releaseyear'])."'
AND title = '".mysql_real_escape_string($_POST['title'])."'";
$selectSql2 = "SELECT * FROM type WHERE type='" . $_POST['type'] . "'";
#
echo $selectSql1;
$result1 = mysql_query($selectSql1, $link) or die(mysql_error());
while($row1 = mysql_fetch_assoc($result1)) {
echo $selectSql2;
$result2 = mysql_query($selectSql2, $link) or die(mysql_error());
while ($row2 = mysql_fetch_array($result2))
{
echo '<pre>';
print_r($row);
echo '</pre>';
}
mysql_close($link);
?>
HTML:
<form action="TCTest8.php" method="post">
<p> </p>
<p> Title:
<select name="title">
<option>-</option>
<option>Devil Drive</option>
<option>Amarillo</option>
<option>The The</option>
</select>
<br/>
</p>
<p>Year of Release
<select name="releaseyear">
<option>Choose Year</option>
<option>1999</option>
<option>2000</option>
<option>2005</option>
<option>2006</option>
</select>
</p>
<p>Actor/Artist Name:
<select name="name" size="3" multiple>
<option>Choose</option>
<option>A</option>
<option>B</option>
<option>E</option>
</select>
</p>
<p> type </p>
<input type="checkbox" value="R&B" name="type[]" />
<input type="checkbox" value="blues" name="type[]" />
<input type="checkbox" value="pop" name="type[]" />
</select>
<p><br/>
<input type="submit" />
</p>
</form>
