UPDATE / variable question

[wkpride]

Hey,
Problem with reading a variable in my UPDATE...SET= formula.

This is the error: Unknown column 'Flintstone' in 'field list'.

The code:
$result = mysql_query("SELECT scname, subnum, cktnum, polenum FROM asubckt");

While($row = mysql_fetch_array($result))
{ $scname =($row['scname']);
$subnum = ($row['subnum']);
$cktnum = ($row['cktnum']);
$polenum = ($row['polenum']);

$q2 = mysql_query("SELECT subname, cktname, scname, subnum, cktnum FROM subckt");


while($dow = mysql_fetch_array($q2))
{ $sub2=($dow['subname']);
$ckt2=($dow['cktname']);
$sc2=($dow['scname']);
$snum2=($dow['subnum']);
$cnum2=($dow['cktnum']);

if (($subnum===$snum2) && ($cktnum===$cnum2))

$q3 = "UPDATE asubckt SET subname= SUBSTR($sub2, 0, 20), cktname= SUBSTR($ckt2. 0, 20)";
mysql_select_db("poledb",$dbh);
mysql_query($q3)

If I echo SUBSTR($sub2, 0, 20) it will show "Keith"
If I... UPDATE asubckt SET subname= 'Keith'... it works fine.
But I can't figure out why the variable will not work. Any help is most appreciated.

Thanks, KP
Thanks!
KP

[Frank_Rizzo]

need some quotes in there:

$q3 = "UPDATE asubckt SET subname='" . SUBSTR($sub2, 0, 20). "', cktname='" . SUBSTR($ckt2. 0, 20) . "'";

[laidbackwebsage]

KP,

I'm just guessing here but I think if your string is shorter than the length you want for your substring, MySQL gets confused.

Try adding this:

my $len1 = strlen($sub2) > 20 ? 20 : strlen($sub2);
my $len2 = strlen($ckt2) > 20 ? 20 : strlen($ckt2);

Then change your update code to:
$q3 = "UPDATE asubckt SET subname= SUBSTR($sub2, 0, $len1), cktname= SUBSTR($ckt2. 0, $len2)";

Let me know if that works..

Kevin J

[wkpride]

$q3 = "UPDATE asubckt SET subname='" . SUBSTR($sub2, 0, 20). "', cktname='" . SUBSTR($ckt2, 0, 20) . "'";

works, sorta. Something is off.

The idea was to update a record. Load the next, find a match and update...

But this is updating every record with the last match found - I know it's finding the correct matches.. but it appears that they all get updated when.. it hits the UPDATE..SET statement.

Should it? Thanks...KP

[rocknbil]

Does this

SUBSTR($ckt2. 0, $len2)

have anything to do with it, which should be

SUBSTR($ckt2, 0, $len2)

[wkpride]

Kevin & rocknbil - No luck - unknown column error. Same as before.... hmmmm