Jump to content


Photo

php/mysql problem


  • Please log in to reply
7 replies to this topic

#1 garry27

garry27
  • Members
  • PipPipPip
  • Advanced Member
  • 161 posts

Posted 20 October 2006 - 10:57 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 can somone take a look

cheers

garry



#2 garry27

garry27
  • Members
  • PipPipPip
  • Advanced Member
  • 161 posts

Posted 21 October 2006 - 03:08 PM

bump

#3 garry27

garry27
  • Members
  • PipPipPip
  • Advanced Member
  • 161 posts

Posted 21 October 2006 - 07:22 PM

all positive feedback is very welcome. it doesn't have to be a solution.  :-\

#4 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,891 posts
  • LocationCanada

Posted 21 October 2006 - 09:33 PM

Hey how about making a specific question and posting just the relevant code instead of just dumping the entire code? No one want to wade through someone elses code to find the relevant section.

As to your problem, you dont have ANY UPDATES in that code. Just SELECT and INSERT. So, I am surprised you are even getting one record to update. But an update is easy, just be sure to ALWAYS use a WHERE clause.

Example:

$sql = "UPDATE 'Event' SET 'eventHeading' = \"New Heading\" WHERE 'pubName' = \"Some Pub\"";
The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#5 garry27

garry27
  • Members
  • PipPipPip
  • Advanced Member
  • 161 posts

Posted 21 October 2006 - 10:00 PM

i meant to say insert, not update. with the current code i can only insert one record for any given pub in the Event table.

if you read from the end of the if statements block below *****validate user input********* to the end of the script, you should see what i am trying to do with it.

i can't see anything wrong with the code or why it's behaving the way it is, which is why i've included the entire function for adding puv events.

thanks in advance

#6 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 21 October 2006 - 10:51 PM

What if you created a loop, e.g:
/************ 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
    while ($sql) {
      $sq2 = mysql_query("insert into Event
                          set eventHeading = '$heading',
					          eventDate = '$date',
						      eventBody = '$body',
						     pubName= '$pubname',
						      pubPostcode='$pubpostcode'");
    }						  
  } 

I'm not sure if that is the most efficient way to do it though or that it will even work. However I am thinking what you want to do requires some kind of loop. I could be totally wrong though.

#7 garry27

garry27
  • Members
  • PipPipPip
  • Advanced Member
  • 161 posts

Posted 21 October 2006 - 11:20 PM

what makes you think that what i want to do requires a loop?  ::)

#8 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 21 October 2006 - 11:32 PM

To loop through each event and add them. Like I said, I could be totally wrong. However, considering that fact that what you have now doesn't do what you want, it is worth a try.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users