help with char to string...

[mxl]

Hello,

 

I have a char(2) field in my MySQL database that contains either one or two characters to depict the day of the month.  It is stored in $day.

 

It seems that, whatever I try to do with the contents of that field, I can't!  ???  Do I have to convert the char(2) to a string?  If so, how?

 

What am I missing?

 

 

Now, I'm trying to pad a '0' or zero in front of the single character fields.  It's not working.  Here's the code snippet.

 

if ( strlen(strval($day)) == 1)

{

$d = str_pad(strval($day) , 1 , '0');

}

else

{

$d = $day;

}

 

$timestamp = $year.'-'.$m.'-'.$d.' '.strval($h).':'.$minutes.':00';

 

$sql = 'UPDATE `ps_events`.`concerts` SET `timeStamp` = \''.$timestamp.' WHERE `concerts`.`pid` = \''.$pid.'\'';

 

$res = mysql_query($sql);

 

echo '<tr><td>'

.$venueName.'</td><td>'

.$weekday.'</td><td>'

.$month.'</td><td>'

.$day.'</td><td>'

.$year.'</td><td>'

.$hour.'</td><td>'

.$minutes.'</td><td>'

.$timestamp.'</td><td>'

.$w.'</td><td>'

.$m.'</td><td>'

.$d.'</td><td>'

.$h.'</td><td>'

.$minutes.'</td></tr>';

}

 

 

The sql query isn't landing in the database either.  I have several hundred records in this table and don't want to have to update them manually if I can get away with doing it with a short script. 

 

Any help would be appreciated because I've looked at this too long.  I can't see the mistake I'm making

 

mxl

 

 

[discomatt]

Char types should return strings. Try simply grabbing one and outputting it with mysql_result.

[PFMaBiSmAd]

Overall, what are you trying to accomplish? What data do you have (and it appear you trying to move it into a DATETIME data type)? There may be an easy and direct way of doing this just using mysql functions. Show your original query above the code you posted and I will bet someone can show you a single UPDATE query that will take your individual pieces of date/time and populate your timestamp with it, without using any php code.

 

For your current problem, after you use str_pad() you must always treat the variable as a string (enclose it in double-quotes.) As soon as you reference the variable by itself, it will be treated as a number and loose the leading zero.

[mxl]

Thank you, PFMaBiSmAd!

 

Here's what happened.  I've worked as a programmer for 15 years, yet I'm new to both PHP and MySQL.  After a serious brain injury (40% of cognitive and memory) and 7 years of recuperating, I'm finally able to program again.  To prove it to myself, I am slowly picking up MySQL and PHP (coming from several other programming languages and years of Lotus Notes / Domino programming).  Successfully being able to create a database, table, records and input form was a real success!

 

I started putting hundreds of concerts and games into a database and am finding that I want to do much more with this data. 

 

My schema follows:

 

CREATE TABLE IF NOT EXISTS `concerts` (

  `pid` varchar(6) NOT NULL default '',

  `event` varchar(25) NOT NULL default '',

  `event_caps` varchar(25) NOT NULL default '',

  `venueName` varchar(25) NOT NULL default '',

  `weekday` varchar(9) NOT NULL default '',

  `month` varchar(9) NOT NULL default '',

  `day` char(2) NOT NULL default '',

  `year` varchar(4) NOT NULL default '',

  `time` varchar( NOT NULL default '', // this is actually varchar eight

  `timeStamp` varchar(19) NOT NULL default '',

  PRIMARY KEY  (`pid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

I tried to make the field timeStamp datatype TIMESTAMP, but that didn't work.  If I can put it into the vachar(19), that's fine too.

 

Here's the code which I wrote in PHP to try to move the text content of weekday and month into numbers, and create the required content for the TIMESTAMP typed field:

 

<?

$conn = mysql_connect ("localhost", "user", "<<password>>") or die('Cannot connect to the database because: ' . mysql_error());

 

mysql_select_db ("mydb", $conn);

 

$result = mysql_query("SELECT * FROM concerts") or die(mysql_error()); 

 

// store the record of the "example" table into $row

$row = mysql_fetch_array( $result );

// Print out the contents of the entry

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<title>Date Conversion</title>

</head>

<body>

 

<table border = '1'>

<td>Venue</td>

<td>Weekday</td>

<td>Month</td>

<td>Day</td>

<td>Year</td>

<td>Hour</td>

<td>Minutes</td>

<td>Timestamp</td>

<td>New Weekday</td>

<td>New Month</td>

<td>New Day</td>

<td>New Hour</td>

<td>New Minutes</td>

</tr>

 

<?

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

 

$pid = $row['pid'];

$venueName = $row['venueName'];

$weekday = $row['weekday'];

 

if ($weekday == 'Sunday') $w = 0;

elseif ($weekday == 'Monday') $w = 1;

elseif ($weekday == 'Tuesday') $w = 2;

elseif ($weekday == 'Wednesday') $w = 3;

elseif ($weekday == 'Thursday') $w = 4;

elseif ($weekday == 'Friday') $w = 5;

elseif ($weekday == 'Saturday') $w = 6;

 

 

$month = $row['month'];

 

// first we'll deal with the month conversion

if ($month == 'January') $m = (int) '1';

elseif ($month == 'February') $m = (int) '2';

elseif ($month == 'March') $m = (int) '3';

elseif ($month == 'April') $m = (int) '4';

elseif ($month == 'May') $m = (int) '5';

elseif ($month == 'June') $m = (int) '6';

elseif ($month == 'July') $m = (int) '7';

elseif ($month =='August') $m = (int) '8';

elseif ($month == 'September') $m = (int) '9';

elseif ($month == 'October') $m = (int) '10';

elseif ($month == 'November') $m = (int) '11';

else $m = 12;

 

$day = $row['day'];

$year = $row['year'];

$time = $row['time']; // contents of the time field now looks like this:  '7:30 PM'

 

$hour = substr($time, 0, 1);

$minutes = substr($time, 2, -3);

$ampm = substr($time , -2);

 

// first get rid of

if ($ampm = "pm")

{

$h = intval($hour) + 12;

}

else

{

$h = str_pad(strval($hour) , 1 , '0');

}

 

 

if ( strlen(strval($day)) == 1)

{

$d = str_pad(strval($day) , 1 , 'p');

}

else

{

$d = $day;

}

 

$timestamp = $year.'-'.$m.'-'.$d.' '.strval($h).':'.$minutes.':00';

 

$sql = 'UPDATE `mydb`.`concerts` SET `timeStamp` = \''.$timestamp.' WHERE `concerts`.`pid` = \''.$pid.'\'';

 

$res = mysql_query($sql);

 

echo '<tr><td>'

.$venueName.'</td><td>'

.$weekday.'</td><td>'

.$month.'</td><td>'

.$day.'</td><td>'

.$year.'</td><td>'

.$hour.'</td><td>'

.$minutes.'</td><td>'

.$timestamp.'</td><td>'

.$w.'</td><td>'

.$m.'</td><td>'

.$d.'</td><td>'

.$h.'</td><td>'

.$minutes.'</td></tr>';

}

?>

 

</table>

<?

$res = mysql_query($sql);

?>

</body>

</html>

 

I assume that there is likely an easier way to do this inside of MySQL through phpMyAdmin that I'm using.  If someone can help me understand how I can achieve my goal of a) understanding how I can really create content for the timestamp field in PHP and/or edit the timestamp field inside of MySQL as a query, I'd be very thankful!