Re: [LUG] php mysql help

[Joe Buckle <joe@xxxxxxxxxxxxxxxx>]

On Sat, 2011-04-23 at 20:56 +0100, Kevin Lucas wrote:
> 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 ' ' ?

i don't know if this will be any use. i'm not 100% clear..

$string = "id1,id2,id3;
$value = "'".implode("','",explode(",",$string))."'"; // translate csv
with '' delimits

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

while ($row = mysql_fetch_array($query)) {
echo "'".implode("','",explode(",",$row))."'"; // output your stuff
}

i'd start with that.. but not too sure, its a rough draft! haha!

> 
> 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