query dynamic, I want to assemble the $query in parts

[wildbill2007]

Hello,

 

 

I'm not a programmer,  :-)  and I have a query question.

 

 

 

I have syslog log files getting logged to a mysql Database in that database I have a table called Syslogd, in that table I have 5 rows MsgDate, MsgTime, MsgPriority, MsgHostname, and MsgText.

 

Then I made a simple form to query the database with any combo of the 5 variables: Date, Time, Priority, Host, and Text.

 

The data from the form is sent to a php script.

 

What I would like is to not use wildcards, but keep the query dynamic.

 

 

This query works if I only want to search based on a date and ipaddress, it returns logs form that host on that date.  (But I want to use any combo of the 5 user input values fromt he form, not just the date and host.)

 

 

$query = mysql_query("SELECT * FROM Syslogd WHERE MsgDate = '".$MsgDate."'

AND MsgHostname = '".$MsgHostname."' LIMIT 0, 50");

 

 

 

 

 

 

To keep the query dynamic, I want to assemble the $query in parts, adding

each condidtion ONLY IF A VALUE HAS BEEN ENTERED.

 

I want to do something like this, but cant figure out how:

 

 

$query = mysql_query "("SELECT * FROM Syslogd WHERE "

 

if ($MsgDate)

  {

        $query = $query . "MsgDate = '" . $MsgDate . "' ";

  }

 

if ($MsgTime)

  {

        $query = $query . " MsgTime = '" . $ MsgTime. "' "; 

  }

 

....etc.

 

 

 

Any thoughts? 

 

 

Thanks!

[Wildbug]

<?php

$query = 'SELECT * FROM syslogd WHERE ';
$vars['Time'] = '0801';

echo $query . implode(' AND ',
        array_map(
                create_function(
                        '$e',
                        'global $vars; return "Msg$e=\'$vars[$e]\'";'
                ), array_keys($vars)
        )
) . "\n";
$vars['Date'] = '20070101';
echo $query . implode(' AND ',
        array_map(create_function('$e','global $vars; return "Msg$e=\'$vars[$e]\'";'),array_keys($vars))) . "\n";

?>

 

The above code produces the following output:

 

SELECT * FROM syslogd WHERE MsgTime='0801'

SELECT * FROM syslogd WHERE MsgTime='0801' AND MsgDate='20070101'

 

First I present a single variable, then two to demonstrate it works.  What you need to do to get this code to work is to have all of your variables/column names in a single associative array (an array with "named" elements) as keys and their values as elements.  Examples in the above code are "Time" and "Date."  The important line is the one that starts with "implode."  It uses a slightly confusing trio of functions to render the text snippets of the array keys plus their values in an SQL-friendly format.  I've formatted it with tabs in the first example for readability, then collapsed it for the two-variable example.

 

Does this help?