Advanced mySQL Querys

Reply

Join Date: Nov 2004
Posts: 1
Reputation: typedef is an unknown quantity at this point 
Solved Threads: 0
typedef typedef is offline Offline
Newbie Poster

Advanced mySQL Querys

 
0
  #1
Nov 9th, 2004
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!
Reply With Quote Quick reply to this message  
Join Date: Oct 2004
Posts: 14
Reputation: fpepito is an unknown quantity at this point 
Solved Threads: 1
fpepito fpepito is offline Offline
Newbie Poster

Re: Advanced mySQL Querys

 
0
  #2
Nov 12th, 2004
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]
Reply With Quote Quick reply to this message  
Reply

This thread is more than three months old.
Perhaps start a new thread instead?
Message:


Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC