Array problem 1

meeble · May 19, 2003

I have a form with these fields:

<select multiple name="name[]">
<select multiple name="jobnumber[]">
<select multiple name="client[]">
<select multiple name="stage[]">

and a couple of non array fields.

The form goes to a script on the next page:

#function to change American date to English

function englishToAmericanDate($str)
{
$elements = explode("-", $str);
$americandate = $elements[2]."-".$elements[1]."-".$elements[0];
return $americandate;
}
$americandate = englishToAmericanDate($firstdate);
$americandate2 = englishToAmericanDate($lastdate);

$db = mysql_connect("localhost", "root&quot

;

mysql_select_db("acwh",$db);

if ($description == "&quot

{$description = '%';}

if ($timetaken == "&quot

{$timetaken = '%';}

# This is a recursive function, which either adds a value into a global array
# for use in the select statement, or recurses if the value is an
# array itself and does the same thing.
function DisassembleArray($key, $val)
{
global $queries;
if((!is_array($val)) && ($val != "&quot

)
{
$queries[] = "$key = '$val'";
} else
{
foreach($val as $foo => $real_val)
{
DisassembleArray($key, $real_val);
}
}
}

# These are the names of the form fields, and they also correspond
# to field names in the table that we want to look through.
$valid_fields = array("name", "jobnumber", "client", "stage&quot

;

$queries = array();
foreach($_POST as $key => $val)
{
if(!in_array($key, $valid_fields)) { continue; }
DisassembleArray($key, $val);
}

# Start of the query.
$query = "SELECT * FROM timesheets where description LIKE '%$description%' AND timetaken LIKE '%$timetaken%' AND entrydate BETWEEN '".$americandate."' AND '".$americandate2."'";
if(count($queries) > 0)
# If something's been selected, we append it to the query.
{
$query .= " and " . implode(" or ", $queries);
}
$query .= " order by entrydate desc";

$result = mysql_query ($query,$db);

If on the form page I choose 'James' and 'Pete' from the staffname box and 'acd000' and 'acd001' from the jobnumber box and search by them and I echo out the SQL statement I get:

SELECT * FROM timesheets where description LIKE '%%%' AND timetaken LIKE '%%%' AND entrydate BETWEEN '1-1-1' AND '03-05-19' and name = 'jamesf4218' or name = 'Pete' or jobnumber = 'acd000' or jobnumber = 'acd001' order by entrydate desc

When what I REALLY want is:

SELECT * FROM timesheets where description LIKE '%%%' AND timetaken LIKE '%%%' AND entrydate BETWEEN '1-1-1' AND '03-05-19' and (name = 'jamesf4218' or name = 'Pete') AND (jobnumber = 'acd000' or jobnumber = 'acd001') order by entrydate desc

How do I change the above code to get this?

Many thanks

James

DRJ478 · May 19, 2003

James,

Just change this line to include the paratheses:

Code:

$query .= &quot; and (&quot; . implode(&quot; or &quot;, $queries).&quot;)&quot;;

meeble · May 20, 2003

Hello,

No, this still gives the same result. The outputted SQL now is:

SELECT * FROM timesheets where description LIKE '%%%' AND timetaken LIKE '%%%' AND entrydate BETWEEN '1-1-1' AND '03-05-20' and (name = 'jamesf4218' or name = 'Pete' or jobnumber = 'acd000' or jobnumber = 'acd001') order by entrydate desc

whereas I want:

SELECT * FROM timesheets where description LIKE '%%%' AND timetaken LIKE '%%%' AND entrydate BETWEEN '1-1-1' AND '03-05-19' and (name = 'jamesf4218' or name = 'Pete') AND (jobnumber = 'acd000' or jobnumber = 'acd001') order by entrydate desc

Any ideas?

Cheers

James

DRJ478 · May 20, 2003

I suggest that you create a multi-dimensional array that is keyed by the fielname:

Code:

$valid_fields = array(&quot;name&quot;, &quot;jobnumber&quot;, &quot;client&quot;, &quot;stage&quot;);

$queries = array();
foreach($_POST as $key => $val){
   # skip other fields
   if(!in_array($key, $valid_fields)) { continue; }
   # build multidimensional array   
   $queries[$key][] = &quot;$key = '$val'&quot;;
}

Later rewrite the array to the SQL condition:

Code:

foreach($queries as $key=>$value){
   # remember the key is the fieldname and value an array
   $query.= &quot; and &quot; . implode(&quot; OR &quot;, $value).&quot;)&quot;;
}

Doing it like the aboce way separates the different fielnames in combined AND (...OR...) blocks.

meeble · May 20, 2003

Hello - thanks for the code.

I have incorporated it into my code so now I have:

function englishToAmericanDate($str)
{
$elements = explode("-", $str);
$americandate = $elements[2]."-".$elements[1]."-".$elements[0];
return $americandate;
}
$americandate = englishToAmericanDate($firstdate);
$americandate2 = englishToAmericanDate($lastdate);

$db = mysql_connect("localhost", "root&quot

;

mysql_select_db("acwh",$db);

if ($description == "&quot

{$description = '%';}

if ($timetaken == "&quot

{$timetaken = '%';}

# This is a recursive function, which either adds a value into a global array
# for use in the select statement, or recurses if the value is an
# array itself and does the same thing.
function DisassembleArray($key, $val)
{
global $queries;
if((!is_array($val)) && ($val != "&quot

)
{
$queries[] = "$key = '$val'";
} else
{
foreach($val as $foo => $real_val)
{
DisassembleArray($key, $real_val);
}
}
}

# These are the names of the form fields, and they also correspond
# to field names in the table that we want to look through.
$valid_fields = array("name", "jobnumber", "client", "stage&quot

;

$queries = array();
foreach($_POST as $key => $val){
# skip other fields
if(!in_array($key, $valid_fields)) { continue; }
# build multidimensional array
$queries[$key][] = "$key = '$val'";
}

# Start of the query.
$query = "SELECT * FROM timesheets where description LIKE '%$description%' AND timetaken LIKE '%$timetaken%' AND entrydate BETWEEN '".$americandate."' AND '".$americandate2."'";
if(count($queries) > 0)
# If something's been selected, we append it to the query.
{
foreach($queries as $key=>$value){
# remember the key is the fieldname and value an array
$query.= " and " . implode(" OR ", $value).&quot

";
}
}
$query .= " order by entrydate desc";

$result = mysql_query ($query,$db);

if ($myrow = mysql_fetch_row($result)) {

etc....

This however, gives an error message:

PHP Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in c:\inetpub\

http://wwwroot\ac\new\timesheet_view2.php

on line 78

What am I doing wrong please?

DRJ478 · May 20, 2003

There's an error in the SQL.
To catch errors like that make your query output the error when it fails:

Code:

$result = mysql_query($query,$db) OR die(mysql_error());

We need to look at the SQL, it's malformed in some way.

meeble · May 20, 2003

Hello,

The error message is:

You have an error in your SQL syntax near ') and jobnumber = 'Array') order by entrydate desc' at line 1

What's wrong with this?!

Cheers

James

DRJ478 · May 20, 2003

Allright,

Here's the compacted code that makes up the SQL as you wanted to have it.

Code:

foreach($_POST as $key => $val){
   # skip other fields
   if(!in_array($key, $valid_fields)) { continue; }
   # $val is an array
   # We need to infuse the variable to be compared to
   $query.= &quot; and ($key='&quot; . implode(&quot;' OR $key='&quot;, $val).&quot;')&quot;;
}

How did we get there?
The important thing about forums like this is to not just get problems solved but also to understand how one arrives at the solution.

1. POST is an associative array. It has everything that we need.
2. Iterate the array and compare it to the fields you want.
3. implode() the content of the values into a SQL statement that is syntactically correct.

To do all this, there are standard steps that one takes:
1. Inspect if the varibles we work with contain the values we expect.
2. Catch any error by using error logging and responses from other involved servers (MySQL etc.)

Now, I left one more thins for you to do.
WHat are you going to do if there is no value in one of the POST vars where you expect one? How can we catch that from producing an error and creating SQL that returns no rows?

Cheers,
DRJ

meeble · May 21, 2003

Hello,

Thanks for you help here.

The code seems to work now.

You said:

"I left one more thins for you to do.
WHat are you going to do if there is no value in one of the POST vars where you expect one? How can we catch that from producing an error and creating SQL that returns no rows?"

However, the code does not seem to produce an error when there is no value in one of the post vars. It works fine.

The code I have now is:

function englishToAmericanDate($str)
{
$elements = explode("-", $str);
$americandate = $elements[2]."-".$elements[1]."-".$elements[0];
return $americandate;
}
$americandate = englishToAmericanDate($firstdate);
$americandate2 = englishToAmericanDate($lastdate);

$db = mysql_connect("localhost", "root&quot

;

mysql_select_db("acwh",$db);

if ($description == "&quot

{$description = '%';}

if ($timetaken == "&quot

{$timetaken = '%';}

$valid_fields = array("name", "jobnumber", "client", "stage&quot

;

$queries = array();
foreach($_POST as $key => $val){
# skip other fields
if(!in_array($key, $valid_fields)) { continue; }
# build multidimensional array
$queries[$key][] = "$key = '$val'";
}

$query = "SELECT * FROM timesheets where description LIKE '%$description%' AND timetaken LIKE '%$timetaken%' AND entrydate BETWEEN '".$americandate."' AND '".$americandate2."'";

foreach($_POST as $key => $val){
# skip other fields
if(!in_array($key, $valid_fields)) { continue; }
# $val is an array
# We need to infuse the variable to be compared to
$query.= " and ($key='" . implode("' OR $key='", $val)."')";
}

$query .= " order by entrydate desc";

$result = mysql_query ($query,$db);

if ($myrow = mysql_fetch_row($result)) {

etc...

Thanks again - I will star you...

DRJ478 · May 21, 2003

meeble,

1. Thanks for the star.
2. The error reporting depends on the settings if your PHP installation.

There is an error when you try to access a non existing key in an array - if it's displayed or not depends on the settings in php.ini
Also, the implode statement produces an error if it is applied to a non-array. It will complain about an invalid second parameter.

meeble · May 23, 2003

>>>>>>>>>>>There is an error when you try to access a non existing key in an array - if it's displayed or not depends on the settings in php.ini
Also, the implode statement produces an error if it is applied to a non-array. It will complain about an invalid second parameter.

Ok, so how do I avoid this then?

Cheers

James

skiflyer · May 23, 2003

Three ways really...

1) Turn down the level of the error reporting in your php.ini

2) Supress the errors on that line

Code:

@$query.= &quot; and &quot; . implode(&quot; OR &quot;, $value).&quot;)&quot;;

3) Handle for the possibility in your code (the most elegant solution IMO)

Code:

if (is_array($value)) {
  $query .= &quot; and &quot; . implode(&quot; OR &quot;, $value).&quot;)&quot;;
} 
else {
  $query .= &quot; and whatever you want here.)&quot;;
}

-Rob

sleipnir214 · May 23, 2003

And keep in mind that skiflyer listed those in reverse order of best solutions.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

skiflyer · May 23, 2003

heh, unintentionally... but indeed I did.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Array problem 1

meeble

Programmer

DRJ478

IS-IT--Management

meeble

Programmer

DRJ478

IS-IT--Management

meeble

Programmer

DRJ478

IS-IT--Management

meeble

Programmer

DRJ478

IS-IT--Management

meeble

Programmer

DRJ478

IS-IT--Management

meeble

Programmer

skiflyer

Programmer

sleipnir214

Programmer

skiflyer

Programmer

Similar threads

Part and Inventory Search

Sponsor