mysql_fetch_array(): supplied argument is not...

[quakerstate79]

Hey all, can any of you gurus spot where this query goes wrong? Looks good to me , but i'm a newb. Thanks in advance for your time and expertise.

Q


SELECT CaseID, Part, Device, Sitename, ContactCountry, CaseStatus, CaseOpenTime, CaseCloseTime FROM opened WHERE 'Owner'='Boris Qin' AND 'Manager'='Alfred Chen'

[CFMaBiSmAd]

If Owner and Manager are column names, don't enclose them in single-quotes.

[quakerstate79]

ill give that a try , and post if it works , thanks


Q

[quakerstate79]

nope didnt work, however, i might have some idea... Do the column names that you use as conditions ie (WHERE 'Owner'='Boris Qin' AND 'Manager'='Alfred Chen') have to be included in the SELECTed columns? Can anyone comment on this?

If so, does anyone know another way in MySQL to just get certain pieces of the data returned w/o having to completely rethink your WHERE statements?

Q

[CFMaBiSmAd]

Do all the column names and the table name match what exists in the database?

This is apparently being done through PHP, there are a number of things that would cause a query to fail. Post your code to receive the best and quickest route to an answer that matches what you are doing.

[quakerstate79]

$filedata = trim($_POST["data"]);


$delimiter = "@^@";
$delimiter2 = "%^P";

$arrayfiledata = explode($delimiter, $filedata);
$count = count($arrayfiledata);




$caseidvalue = explode("=",$arrayfiledata[1]);

$columns_to_display = $arrayfiledata[$count-2];//columns to display
$columns_to_display = explode("=",$columns_to_display);
$columns_to_display = $columns_to_display[1];
$columns_to_display = explode($delimiter2,$columns_to_display);
$count2= count($columns_to_display)-1;


$query = "SELECT ";

if($count2<=0)$query = $query . "* FROM opened ";
else
{
for($i=0;$i<$count2;$i++) //adds fields to display if count2==0, adds "*"
{
$d = mysql_real_escape_string($columns_to_display[$i]);
$query = $query.$d;
if($i<$count2-1)$query = $query . ", ";
else $query = $query . " ";
}

$query = $query . "FROM opened ";
}

$dropdownmenuitems = array_slice($arrayfiledata,2,$count-4);

$countmi = count($dropdownmenuitems);
for($i=0;$i<$countmi;$i++)/////////////////////creates 2D array with all the menu items menutitemname is [0] and value is [1]
{
$dropdownmenuitems[$i]= explode("=",$dropdownmenuitems[$i]);
}


$value_flag=false;

for($i = 0;$i<$countmi;$i++)
{
$name = mysql_real_escape_string($dropdownmenuitems[$i][0]);
$value = mysql_real_escape_string($dropdownmenuitems[$i][1]);

if($value != "all")
{
if($value_flag == false)
{
$value_flag = true;
$query = $query . "WHERE ";
}

$query = $query . "" . $name . "='" . $value . "' AND ";

}
}
if($value_flag)
{
$n = strlen($query);
$query = substr($query,0,$n-4);


}


echo $query;


//echo var_dump($dropdownmenuitems);

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

while($row = mysql_fetch_array($result, MYSQL_BOTH))
{

echo $row['CaseID'];
echo $row['Device'];
}


$fh = fopen("info.txt", 'w')or die("file didnt open");
fwrite($fh,$query)or die("file didnt write");
fclose($fh) or die("file didnt close");


the thing i posted earlier was the contents of "info.txt" so im pretty sure that s the exact query going in the DB, anyways, you asked for the code, here it is.

Q

PS could someone respond to the question I had in my post before this one? I really think that has something to do with it...

[CFMaBiSmAd]

The information you SELECT and the conditions in your WHERE clause only have to exist. You can SELECT abc FROM my_table WHERE def = 'ghi'.

I don't see any logic that connects to a mysql server and selects a database. These could be causing the query to fail.

There is also no error checking and reporting for the query. Change your mysql_query(...) statement to this -
$result = mysql_query($query, $connection) or die('Query Failed:' . mysql_error());

[quakerstate79]

i connect in a way that i know works, i didnt show that part because it doesnt have anything to do with the query formation. But, i know for a fact that i dont have any problems with my DB connection. Let me try this and see what error i get. Hold on


Q

[quakerstate79]

ok, that code snippet with the error checking was huge. I'll definitely include that on any DB call from now on. It let me get right to the heart of the problem. I had a field in my DB called "CaseCreateTime" and i was trying to access it with "CaseOpenTime". Thankyou so much.


Q <---- sheepishly backs out of forums

[miller]

Adding the or die('Query Failed:' . mysql_error()); is definitely a good idea for all queries, but you also would have discovered the error if you had simply tried to run this query manually at a SQL prompt. It always helps to narrow down where your problem is by going back to basics.