updating mysql table from php mysql query result

format text to date and update tbale with formatted date

[randy1as]

Converted excel xls to mysql table. The dates were entered as 121208 for 12 Dec 2008 and 21208 for 2nd Dec 2008.
Can format the date using substr function and strlen and show the result with while statement - but how do I update the field - tried update SQL in the while statement didn't work. Help. I am factory fresh to MySql and PHP.
<?php
$link = mysql_connect('localhost', 'root', 'root');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db('test');
if (!$db_selected) {
die('Could not select database: ' . mysql_error());
}


$query = "SELECT * FROM pers ";
$result = mysql_query($query);

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

$DateMinusDash = $row [TOS];
/*$Pxno = &row[PersNo];*/
if (strlen($DateMinusDash) == 6)
$DateWithDash = (substr ($DateMinusDash,0,2).'-'.substr ($DateMinusDash,2,2).'-'.substr ($DateMinusDash,4,6));
$row [TOS] = $DateWithDash;

/*mysql_free_result($result);*/

/*$sql = "UPDATE pers SET TOS= $DateWithDash WHERE PersNo= $Pxno";*/

/*$result= mysql_query($sql);*/
echo $row["PersNo"]. $row["Name"] . " " .$row [TOS], "<br />\n";
/*mysql_free_result($result);*/
/*else
echo "false"*/
/*elseif (strlen($DateMinusDash) == 5)*/
/*echo $row["Name"] . " " .$row [TOS]."five", "<br />\n"; */
/*$strn= "abcdef";
/*$length = strlen(utf8_decode($strn));*/
/*if $length == 6;*/
/*$len = strlen($strn);
if ($len = 6)
echo $len;*/
}
?>

[jd01]

Try:

$sql = "UPDATE pers SET TOS='".$DateWithDash."' WHERE PersNo= $Pxno";

OR

$sql = "UPDATE pers SET TOS='".$DateWithDash."' WHERE PersNo='".$Pxno."'";

You need to quote ' (single) your strings... If $Pxno is a 'string' or 'text' type EG varchar, char, etc. and not int or a numeric type, you will need to quote it too.

That's the first thing I see at a glance. Have a try and let us know how it goes... NOTE: I used concatenation on the variables in the string, because they parse much faster and take less overhead to process. You could technically write it like this and achieve the same result:

$sql = "UPDATE pers SET TOS='$DateWithDash' WHERE PersNo='$Pxno'";

ADDED:
I completely didn't notice this was your first post:
Welcome to WebmasterWorld!

[randy1as]

Thanks
tried it out
produces this
Parse error: parse error, expecting `T_PAAMAYIM_NEKUDOTAYIM' in Z:\www\eg\strlen.php on line 18

Spent the better part of the day worrying this problem - saw something about moving the the second query statement out of the while loop with prepare and execute something to do with PBO.

Need a drink - will work on this next Sunday. thanks again.

[jd01]

Without seeing the exact code I couldn't really tell you... It means expecting a :: double-colon. I've managed to generate one before, but don't remember exactly how. Could be as simple as trying to run some type of function on a variable you forgot the $ on.

[randy1as]

Eureka!
One - discovered used a debugger (Expert Debugger from [nusphere.com...] on the bugger) DBGbar on Mozilla works like a dream occasionally Apache protests and shuts down.

Two - had insomnia worked from 2 am through 6 am and am zonked.

three the soln

<?php
// set up connection
$link = mysql_connect('localhost', 'root', 'root');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
//the database
$db_selected = mysql_select_db('test');
if (!$db_selected) {
die('Could not select database: ' . mysql_error());
}

//query the data file
$query=" SELECT * FROM Main ";
// throw it to an array
$result=mysql_query($query);
// set up a counter
$num=mysql_numrows($result);

// initiate the counter
$i=0;
// loop the loop
while ($i < $num) {
// remember the unique value for update later
$id=mysql_result($result,$i,'PersNo');

//extract the unformatted value of field
$DateMinusDash = mysql_result($result,$i,'TOS');
//check strlen for proper format and be selective
if (strlen($DateMinusDash) == 6){
// format as required
$DateWithDash = (substr ($DateMinusDash,0,2).'-'.substr ($DateMinusDash,2,2).'-'.substr ($DateMinusDash,4,6));
$query1="UPDATE Main SET TOS='$DateWithDash' WHERE PersNo='$id'";
// fire the update query
mysql_query($query1);
//increase the counter by one
++$i;
}
elseif (strlen($DateMinusDash) == 5){
$DateWithDash = ('0'.substr($DateMinusDash,0,1).'-'.substr ($DateMinusDash,1,2).'-'.substr ($DateMinusDash,3,6));
$query1="UPDATE Main SET TOS='$DateWithDash' WHERE PersNo='$id'";
mysql_query($query1);
++$i;
}
else{
++$i;
}
// cross the fingers
}

?>

four - didn't get the hang of PDO / Pear.

Thank you all who cared to reply