[LUG] php mysql help

[Kevin Lucas <kevin.lucas@xxxxxxxxxxxxxxxxxx>]

Hi all hope you are enjoying this Easter sunshine break?

I am pondering over something that I have wanted to achieve for some
time with my LAMP system to run the Shop.

In a nutshell I have a "multicheck box" form which I select many items
then  post it to a php script to fetch the complete details of the item
from the Db then add quantities and  put in a Temp table in Mysql.

I have the problem of the post index "box" is mostly numeric ie 123456
format but there are a few valid id's like SP123456 or even AbcDefGHIJK
in some cases. 
The table schema has this "box" as being VARCHAR(20) so it should handle
all these chars.

and it's these alpha chars that give me a problem.

the php I have tried is below 

//this first bit gets the string in a var called $val

echo "<br> get the checkboxes in a while list<br>";

$box=$_POST['box'];
echo "you have chosen ";
while (list ($key,$val) = @each ($box)) {
echo "'$val',";
}

// now I can see I have all the checkbox id's and try to get them in a
format to use the SELECT... FROM ....WHERE ...  IN type query

echo "<br> implode results";
// if ok need to put code in select query
$code = "(".implode(",", $_POST['box']).")";
echo "$code";
echo "<br>";


$query="select  code, product, product2 FROM shelflables WHERE code IN
$code  group by code order by code";
$result=mysql_query($query);


///diag to see if the query worked 
echo "<br> raw output of query to see whats fetched <br> ";
if ($dbResult = mysql_query($query))
{
   while($recordData = mysql_fetch_assoc($dbResult))
   {
      print_r($recordData); // output raw contents of $recordData
   }
}
else
   print('Could not SELECT data from database. Query sent:
'.$query.'<br/>'."\n".'Error given: '.mysql_error().'<br/>'."\n");
///
$num=mysql_numrows($result);

mysql_close();


//this bit is a life saver as it displays the error on the web page
instead of me looking at the logs.

The var $code works without any modification for all numeric records.

If a Alpha char is in the string the format (12345,12346,AP12345) fails
on 

 Could not SELECT data from database. Query sent: select code, product,
product2 FROM shelflables WHERE code IN (12345,12346,AP12345) group by
code order by code
Error given: Unknown column 'AP12345' in 'where clause'

If I manually type in the query and put each element in a ' ' the query
runs OK.    Why does the code run for numeric but not Alpha chars?

So I have got as far as this with $check_val replacing the $code var 


//another method
echo "<br> loop round count of checkboxes and add  (comma and ' ') ";
if(isset($_POST['box']))
{ $check_val .="('"; //adds the opening bracket
for ($i=0; $i<count($_POST['box']);$i++) {
//here I am trying to create a String in the form
 //('id1','id2','id3')


 $check_val .= $_POST['box'][$i];
 $check_val .="','"; 
 
}$check_val .=")";
}
echo "<br>";
echo $check_val;
echo "<br>";

//but I get a ,' left at the end so the query wont run 

So is there an easier way of collecting the string together with commas
between and delimited by ' ' ?

Apologies for the length of this but I hope I have explained it
correctly! 
 
-- 
Regards

Kevin Lucas
Minions Post Master(Sub) 
Eleven Years in the Making!
www.minionsbandb.co.uk
www.tearooms.minionsbandb.co.uk
FaceBook Minions_shop
Po House, Minions,
Liskeard Cornwall 
PL14 5LE
01579363386


-- 
The Mailing List for the Devon & Cornwall LUG
http://mailman.dclug.org.uk/listinfo/list
FAQ: http://www.dcglug.org.uk/listfaq