Advanced mySQL Querys
 

Hi, im new to the forums and i hope you wont mind me launching in with a big question!

Ii am currently prototyping a multimedia information
system which holds wakeboarding images and videos, along
with a comprehensive set of meta-data about the multimedia
data. Users can query the metadata in the database extensivly to return relevant pictures/videos. (ie. search by wakeboarder, lake , date taken etc.)
This is an online system, using mySQL for the
meta-data-database and PHP to work with it.

I put the database in 1st normal form. Meaning that my data
is now stored across 14 separate relational tables, no
repeating fields... and 10 line SQL querys with about 7
inner joins :(. Being lazy i made a duplicate database in
Access and have been using this to generate SQL commands
that would otherwise make my head explode .

A quick think and i come across this problem (being
relatively new to database design). I want to query
anywhere between 1 and 8 fields at a time from a single PHP
Search form.
Ideally i will be blissfully unaware of a very simple "one
template" solution i can use to get results from the
database i need... to further explain ...
i was hoping to utilise one standard SQL command template
which takes user inputted values from a PHP form and querys
the DB with those values, but ignores the blank ones. From
using my access model, it seems that if i search for a
blank ( eg. "" as criteria) along with some valid search
criteria i know to give a result (such as 22 for RiderAge or Mike for RiderName )
it returns no result. however the same search with a
*completely empty box* (instead of "") for the blank fields
returns me standard results.
Is this simply just an access thing? when i send a $string
to mySQL database from the PHP form containing nothing will it act as if i
queried "" or left it blank?

Cause other wise i need to write 92 separate templates
( 8
+7+6+5+4+3+2+1
+6+5+4+3+2+1... etc. )

THe only way around this if i am stuck i can see would be
to create a stupidly long php script for the search page
with 92 Separate SQL Command Templates, and write an
algorithm decide which to use based on which criteria had
been entered and which left blank, which to me, sounds like
a lot of work. (Do-able, but would eat an entire afternoon)

I hope this makes sense and some-one can tell me an better
solution!

Cheers!

Re: Advanced mySQL Querys
 

Hello typedef,

I don't know if I have understand all of your problem, here a way to constroy a SQL with various fields

fpepito


######## http://www.fpepito.org/php/test6.php

[PHP]
<?

if (!isset ($autor)) {
echo "<FORM ACTION=$PHP_SELF>\n";
echo "Autor : <INPUT TYPE=TEXT NAME=autor VALUE=Pepito><BR>\n";
echo "Text : <INPUT TYPE=TEXT NAME=text VALUE=port><BR>\n";
echo "After date : <INPUT TYPE=TEXT NAME=aft VALUE=2002-01-01><BR>\n";
echo "Before date : <INPUT TYPE=TEXT NAME=bef VALUE=2004-01-1><BR>\n";
echo "<INPUT TYPE=submit value=\"Identify\">\n";
echo "<BR><HR><BR>\n";
echo "</FORM>\n";
} else {
include "/home/httpd/html/PHP/connect.php";
connect_mysql();
$criteria = "";
if (isset ($autor) && !($autor == "")) {
$criteria .= " autor = '$autor'";
}
if (isset ($text) && !($text == "")) {
if (! ($criteria == "")) {$criteria .= " AND "; }
$criteria .= " desc_pt LIKE '%$text%'";
}
if (isset ($aft) && !($aft == "")) {
if (! ($criteria == "")) {$criteria .= " AND "; }
$criteria .= " data > '$aft'";
}
if (isset ($bef) && !($bef == "")) {
if (! ($criteria == "")) {$criteria .= " AND "; }
$criteria .= " data < '$bef'";
}



if (!($criteria == "")) {
$criteria = "WHERE ". $criteria;
}
$q = "SELECT * FROM fotos_file $criteria";
echo "$q<BR>\n";
$query = mysql_query ($q);
$res = mysql_num_rows($query);
echo "<BR>I found $res element corresponding of your demand";
}

?>
[/PHP]