homepage Welcome to WebmasterWorld Guest from 50.17.86.12
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
updating mysql table from php mysql query result
format text to date and update tbale with formatted date
randy1as

5+ Year Member



 
Msg#: 4000715 posted 2:45 am on Oct 4, 2009 (gmt 0)

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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4000715 posted 3:42 am on Oct 4, 2009 (gmt 0)

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

5+ Year Member



 
Msg#: 4000715 posted 1:12 pm on Oct 4, 2009 (gmt 0)

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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4000715 posted 7:53 am on Oct 5, 2009 (gmt 0)

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

5+ Year Member



 
Msg#: 4000715 posted 1:21 am on Oct 9, 2009 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved