Page 1 of 1

2 basic Questions

Posted: Sat Dec 15, 2007 9:50 am
by newbie2php
Hi all,

1) How do you determine whether an INSERT or UPDATE was successful? I had a look and thought it was done via mysql_num_rows but this doesn't seem to be working for me

e.g

Code: Select all

$ourquery = "INSERT INTO tableone (username, city) VALUES ('$username','$city')";
$excute = mysql_query($ourquery);
if (mysql_num_rows($excute ) > 0)
{
echo "success";
}
else
{
echo "could not enter details";
{
Basic example. But I need to be able to relay back to the user if it was successful or not.

2) Inserting a date of birth into a date formatted MYSQL column (yyyy-mm-dd)?

I have drop down menus to choose date of birth, and then have a function that changes these 'pretty' months ('jan 'etc..) into plain numbers, e.g output would be.... 19970222 for 22 feb 1997

But it doesn't seem to update properly. Date functions aren't my thing at all!

Heres my function (could be alot tidier, but want to make it work first!)

Code: Select all

function convert_dob_to_mysqldate($year,$month,$day)
{
	if ($month == "jan") {$output_month = 01;}
	if ($month == "feb") {$output_month = 02;}
	if ($month == "mar") {$output_month = 03;}
	if ($month == "apr") {$output_month = 04;}
	if ($month == "may") {$output_month = 05;}
	if ($month == "jun") {$output_month = 06;}
	if ($month == "jul") {$output_month = 07;}
	if ($month == "aug") {$output_month = 08;}
	if ($month == "sep") {$output_month = 09;}
	if ($month == "oct") {$output_month = 10;}
	if ($month == "nov") {$output_month = 11;}
	if ($month == "dec") {$output_month = 12;}
	
	if ($day == "1") {$output_day = 01;}
	if ($day == "2") {$output_day = 02;}
	if ($day == "3") {$output_day = 03;}
	if ($day == "4") {$output_day = 04;}
	if ($day == "5") {$output_day = 05;}
	if ($day == "6") {$output_day = 06;}
	if ($day == "7") {$output_day = 07;}
	if ($day == "8") {$output_day = 08;}
	if ($day == "9") {$output_day = 09;}
	if ($day == "10") {$output_day = 10;}
	if ($day == "11") {$output_day = 11;}
	if ($day == "12") {$output_day = 12;}
	if ($day == "13") {$output_day = 13;}
	if ($day == "14") {$output_day = 14;}
	if ($day == "15") {$output_day = 15;}
	if ($day == "16") {$output_day = 16;}
	if ($day == "17") {$output_day = 17;}
	if ($day == "18") {$output_day = 18;}
	if ($day == "19") {$output_day = 19;}
	if ($day == "20") {$output_day = 20;}
	if ($day == "21") {$output_day = 21;}
	if ($day == "22") {$output_day = 22;}
	if ($day == "23") {$output_day = 23;}
	if ($day == "24") {$output_day = 24;}
	if ($day == "25") {$output_day = 25;}
	if ($day == "26") {$output_day = 26;}
	if ($day == "27") {$output_day = 27;}
	if ($day == "28") {$output_day = 28;}
	if ($day == "29") {$output_day = 29;}
	if ($day == "30") {$output_day = 30;}
	if ($day == "31") {$output_day = 31;}
	
	$output_date = $year.$output_month.$output_day;
	return $output_date;
}
This function seems to work for months and days which do not have a proceeding '0' - so for 18 dec 1967 it would work fine, but if I choose a month or day such as 7 jan 1967 it will not - but I thought the date format wants leading zeros :?

Posted: Sat Dec 15, 2007 10:14 am
by Oren
mysql_affected_rows() may be of interest.

Also:

Code: Select all

if ($excute) echo 'success';

else echo 'failure';
As for the date, MySQL DATE type wants a string of the form: YYYY-MM-DD, and not a timestamp.
Use: date() together with strtotime() :wink:

Posted: Sat Dec 15, 2007 10:15 am
by Chalks
I don't really know the answer to your question, but I do know that one step towards making your code a bit more efficient would be to use a switch statement.

Code: Select all

function convert_dob_to_mysqldate($year,$month,$day) 
{ 
  switch ($month)
  {
    case "jan":
      $output_month = 01;
      break;
    case "feb":
      $output_month = 02;
      break;
    case "mar":
      $output_month = 03;
      break;
    //etc...
  }
}

Posted: Sat Dec 15, 2007 10:29 am
by Oren
Or just...

Code: Select all

$date_ready_for_insert_into_db = date('Y-m-d', strtotime("$year - $month - $day"));

Posted: Sat Dec 15, 2007 10:45 am
by Chalks
well... yeah. Switch statements are so much fun though! ;)

Posted: Sat Dec 15, 2007 11:16 am
by newbie2php
Oren wrote:Or just...

Code: Select all

$date_ready_for_insert_into_db = date('Y-m-d', strtotime("$year - $month - $day"));
Are you sure that is correct? I tried it and still didn't get anything entered in the column, just 000-00-00

If we have the days, the months and years with the correct amount of numbers i.e leading zeros for single digits with days and months, and years as 4 digits, what functions do I have to perform to make the table accept this for date of birth (as a date format)?

I can't see why the column doesnt just accept it as $year."-".$month."-".$day ?

Posted: Sat Dec 15, 2007 11:17 am
by John Cartwright
Probably nothing to worry about, but mysql_affected_rows() will return 0 if the update does not actually change the contents of the row, i.e. the contents are the same.

So do not assume that if mysql_affected_rows() returns 0 there was an error.

Posted: Sat Dec 15, 2007 11:19 am
by Oren
Read what the manual says about the 2 functions which I suggested.

Posted: Sat Dec 15, 2007 3:08 pm
by superdezign
Jcart wrote:So do not assume that if mysql_affected_rows() returns 0 there was an error.
Right. You can use mysql_info() to get the number of rows *matched* instead of the number of rows *affected,* though. If you don't know what "matched rows" are, it's like affected rows, but also counts the rows that were technically "updated," even though they weren't actually changed.

Code: Select all

$info = mysql_info($this->__connection);
$matchedRows = (int)preg_replace('&^Rows\smatched:\s(\d+).*&', '\\1', $info);
Just an FYI. ^_^

Posted: Sat Dec 15, 2007 3:13 pm
by John Cartwright
Didn't know about the mysql_info() function. Thanks.