Jump to content


Photo

php/mysql problem - why can i insert ony one record?


  • Please log in to reply
1 reply to this topic

#1 garry27

garry27
  • Members
  • PipPipPip
  • Advanced Member
  • 161 posts

Posted 21 October 2006 - 07:43 PM

hi,

i've written a script that updates data into a Events table in mysql via a form which has info regarding events (date, heading and body) that i plan to update onto a calendar later on.

the current problem i have is that using the current code, it will only update a single record for any given pub into the Events table.

here's the relivant mysql tables:

TABLE Pub(
userEmail               VARCHAR(25) NOT NULL,
pubName                 CHAR(20) NOT NULL,
pubStreetNo             SMALLINT NOT NULL,
pubStreet               VARCHAR(20) NOT NULL,
pubCity                 VARCHAR(20) NOT NULL,
pubCounty               CHAR(20) NOT NULL,
pubPostcode           CHAR(8 ) NOT NULL,
pubPhone                BIGINT NOT NULL,
PRIMARY KEY(pubName, pubPostcode, userEmail));


TABLE Event(
pubName                    CHAR(20) NOT NULL,
pubPostcode              CHAR(8 ) NOT NULL,
eventDate                  DATE NOT NULL,
eventHeading               VARCHAR(25) NOT NULL,
eventBody                  CHAR(20) NOT NULL,
PRIMARY KEY(pubName, pubPostcode));

and the php function that validate and adds the event:


   function add_event()
{
  // connect to db
  db_connect();

  $day = stripslashes ( trim ( $_POST['day'] ) );
  $month = stripslashes (trim ( $_POST['month'] ) );
  $year = stripslashes ( trim ( $_POST['year'] ) );
  $heading = stripslashes (trim ( $_POST['heading'] ) );
  $body = stripslashes ( trim ( $_POST['body'] ) );

/********** validate user input ********************************************************/  
  
  // format date for mysql
  $date =($year."-".$month."-".$day);

  if ( !checkdate((int)$day, (int)$month, (int)$year) )
  { 
  echo 'Please type the date in the correct format (DD/MM/YYYY)';
  } 
  // notice! - if heading missing
  if ($heading == '')
    echo 'please enter a name for your event';
  // notice! if main text body missing
  if ($body == '')
    echo 'please enter a few details for your event';
	
	
	
  // assign variable to logged on useremail
  $email =  $_SESSION['valid_user']; 
  
  // get pubName of 'valid user'
  $sql = mysql_query ( "select pubName from Pub where userEmail='$email'" ); 
  $row = mysql_fetch_row($sql);
  $pubname = $row[0]; 
  if (!$sql)
  {
    die('Invalid query: ' . mysql_error());
    echo 'Could not add event at this time - please try later.';
	return;
  }
  
  // get pubPostcode of 'valid user'
  $sql = mysql_query ( "select pubPostcode from Pub where userEmail='$email'" ); 
  $row = mysql_fetch_row($sql);
  $pubpostcode = $row[0]; 
  if (!$sql)
  {
    die('Invalid query: ' . mysql_error());
    echo 'Could not add event at this time - please try later.';
	return;
  }
  
  
   //check if calendar slot is taken 
  $sql = mysql_query("select * from Event where eventDate='$date' and pubName='$pubname' and pubPostcode='$pubpostcode'"); 
  $num_rows = mysql_num_rows($sql);
  if (!$sql)
  {
    die('Invalid query: ' . mysql_error());
    echo 'Could not add event at this time - please try later.';
	return;
  }
  if ($num_rows >0 )
  {
    echo 'You already have an event for that day!';
	return;
  } 
  
/************ add date/event if ok  ***************************************/
  
  //specify pub in pub table
  $sql = mysql_query("select * from Pub 
					   where pubName= '$pubname'
					   and pubPostcode='$pubpostcode'");
  if (!$sql)
  {
    die('Invalid query: ' . mysql_error());
    echo 'Could not add event at this time - please try later.';
	return;
  }
  else
  {
   // add event
  $sq2 = mysql_query("insert into Event
                      set eventHeading = '$heading',
					      eventDate = '$date',
						  eventBody = '$body',
						  pubName= '$pubname',
						  pubPostcode='$pubpostcode'");
						  
  } 
}

i know a lot of the data in the Event table is redundant and could to with being broken down but besides that, i can't see any logical reason why i can't update more than one record per pub.

please someone help me with this

garry


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 October 2006 - 08:26 PM

There are a bunch of things wrong here:

1) You should probably use a bona fide calendar picker for dates, otherwise you can get garbage input.

2) When checking pubPostcode, you pull the value out of the first row before you check if your query was successful.  Same for pubName -- and you are wasting a query here, you could check both together.

3) When you check for the calendar slot, you're retrieving a big record set for no reason, since all you want is the counts.  Use COUNT(*), don't just SELECT *.

4) When you add the date (your actual question), you run a query, and then do nothing with it.  You don't use any values, and you don't iterate though the results (the answer to your question).  I'm confused.

5) And yes, the tables aren't normalized.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users