data being lopped off

longbow486

im having some trouble with a script im working on, the problem is that its choppin g off the first two 00's on the mac address, yet it prints it out fine. its only when its being passed to MySQL that it looses the first two 00's
this is the out put i get:

00:18:F8:BD:F0:E3
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':18:F8:BD:F0:E3' at line 1

Thanks for the help
-Phil

<?PHP
$source = "test.txt";
$return = file($source);
$conn = mysql_connect("host", "user", "password");
mysql_select_db("wifi",$conn);

foreach($return as $ret)
{
$wifi = explode ("|", $ret, 17);
$mac = $wifi[1];
echo $mac;
$sql = "SELECT*FROM`wifi`WHERE`mac`=$mac";
$result = mysql_query($sql, $conn) or die(mysql_error());
while ($newArray = mysql_fetch_array($result))
{
$test=$newArray['mac'];
$id=$newArray['id'];
if ( $test == $mac )
{
$sqls ="UPDATE `wifi` SET `ssid` = '$wifi[0]' WHERE `id` = '$id' AND `ssid` = '$wifi[0]' AND `mac` = '$wifi[1]' AND `manuf` = '$wifi[2]' AND `auth` = '$wifi[4]' AND `encry` = '$wifi[5]' AND `radio` = '$wifi[6]' AND `chan` = '$wifi[7]' AND `lat` = '$wifi[8]' AND `long` = '$wifi[9]' AND `btx` = '$wifi[10]' AND `otx` = '$wifi[11]' AND `fa` = '$wifi[12]' AND `la` = '$wifi[13]' AND `nt` = '$wifi[14]' AND `label` = '$wifi[15]' AND `signal` = '$wifi[16]'";

}
}

}
?>

wilku

It should be

$sql = "SELECT*FROM`wifi`WHERE`mac`='$mac'";

Without quotes mysql thinks 00 is a numer and complain about ":" and the rest of mac as inapropriate here.

I don't get what you're trying to do here - first you select all rows with a certain mac then check if mysql did a good job and didn't get you by any chance a different mac? ($test will equal $mac in all cases)
I think the update query will be sufficient.

longbow486

I didnt really clean that script at all, I was just trying a few things out. but I cleaned it up, and added what you have shown me, but im getting another problem, when it updates the row, it sets the ssid to either 0 or 1. as if its seeing it in boolean.
as you can see here:http://randomintervals.com/test/wifi/wifidb/

<?php
$source = "test.txt";
$return = file($source);
$conn = mysql_connect("localhost", "root", "inserthere");
mysql_select_db("wifi",$conn);

foreach($return as $ret)
{
$wifi = explode ("|", $ret, 17);
$mac = $wifi[1];
$sql = "SELECT*FROM`wifi`WHERE`mac`='$mac'";
$result = mysql_query($sql, $conn) or die(mysql_error());
while ($newArray = mysql_fetch_array($result))
{
$id=$newArray['id'];
$signal=$newArray['sig'];
echo $wifi[0].'<BR>';
$sqls ="UPDATE `wifi` SET `ssid` = '$wifi[0]' AND `mac` = '$wifi[1]' AND `manuf` = '$wifi[2]' AND `auth` = '$wifi[4]' AND `encry` = '$wifi[5]' AND `radio` = '$wifi[6]' AND `chan` = '$wifi[7]' AND `lat` = '$wifi[8]' AND `long` = '$wifi[9]' AND `btx` = '$wifi[10]' AND `otx` = '$wifi[11]' AND `fa` = '$wifi[12]' AND `la` = '$wifi[13]' AND `nt` = '$wifi[14]' AND `label` = '$wifi[15]' AND `signal` = '$wifi[16]'WHERE `id` = '$id'";
if (mysql_query($sqls, $conn) or die(mysql_error()))
{echo "text record updated!<br>";} else {echo mysql_error();}
}

}
?>

wilku

To debug your query echo $sqls and see what comes after SET ssid = . If it looks ok, check what type ssid field is.

longbow486

as you can see in the script i have it print out what the value of the variable is, and its the same one being passed to the SQL query. the print out is fine, its just when it gets passed to MySQL. the field type is varchar(50). so i still dont understand how it is thinking its Boolean.

NogDog

Use commas to separate the field/value pairs to be set, not 'AND'. As currently written, you are setting the first field to the boolean result of all those expressions being joined by the logical "and" operator.