Keeping a counter
*Here is what I am trying to acheive: * Basically I have a form where people can submit events to our database. In the CMS I have a page which displays a record of the number of events.
*Here is what I have: * After the button is clicked, this script is called:
if($subject_type == 'Event') {
$query = "SELECT town, update_id, event_validex ";
$query .= "FROM dev_town ";
$query .= "LEFT JOIN updates ON dev_town.town_id = updates.town ";
$query .= " WHERE sitename = '".SITENAME."'";
$query .= " AND month = " .date('m')." AND year =" .date('Y');
$querys = $this->tep_db_query($query);
$rows = $this->tep_db_fetch_array($querys);
extract($rows); //extract rows, so you don't need to use array
$eventid = $event_validex + 1;
$sql_data_array = array('event_validex' => $eventid);
$submit_to_database = $this->tep_db_perform('updates', $sql_data_array, 'update', "town='".$town."'");
This works fine, however I cant seem to solve the next bit
This is the Problem As you can see, it checks the database for the current month and adds it, this is pro开发者_如何转开发viding that the sitename and that month are there, not a site and another month.
How would I get it to add the row in IF the sitename and month are not there?
I have been manually adding the months in now so that it works, and I am sure you can agree that's a ball ache.
Cheers peeps
- if you want to check if site A + Month 11 exists do a select query against it and store the number of rows returned in a variable. ( $exists = mysql_num_rows("your query here"); )
then do an if statement against the $exists variable and proceed as you wish
if($exists) {
// update
} else {
// add
}
$insert = "INSERT INTO updates ('town','month','year','event_validex') VALUES ('".$town."','". date('m')."','". date('Y')."','1')"; $eventid = 1; $sql_data_array = array('event_validex' => $eventid); $submit_to_database = $this->tep_db_perform('updates', $sql_data_array, 'update', "town='".$town."'"); } }
this is what I have for the else statement there, however it will add one to the value if its there but will not add a new entry if its isnt.. ?
I don't see exactly how your method "checks the database for the current month and adds it "; I'll just assume that the tep_db_perform() method of your class handles this somehow.
(uhk! n00bed it; rest of the post was somehow chopped off?) Since you're already hitting the database with the select with the intent of using the data if a record is found, then you could use the resultset assigned to $rows as a means of checking if a record exists with SITENAME and Month.
See below:
if($subject_type == 'Event') {
// build query to check the database for sitename, month and year.
$query = "SELECT town, update_id, event_validex ";
$query .= "FROM dev_town ";
$query .= "LEFT JOIN updates ON dev_town.town_id = updates.town ";
$query .= " WHERE sitename = '".SITENAME."'";
$query .= " AND month = " .date('m')." AND year =" .date('Y');
// Execute Query(wrapper for $result = mysql_query I guess?)
$querys = $this->tep_db_query($query);
// Get a resultset from database. --> you could merge this into one method with $this->tep_db_query
$rows = $this->tep_db_fetch_array($querys);
if(count($rows) > 0) {
extract($rows); //extract rows, so you don't need to use array --> I try to stay away from extract() as it makes for random variables being created.
$eventid = $event_validex + 1;
$sql_data_array = array('event_validex' => $eventid);
$submit_to_database = $this->tep_db_perform('updates', $sql_data_array, 'update', "town='".$town."'");
} else {
// insert new record into database
// updated with code to execute insert SQL query.
$insert = "INSERT INTO updates ('town','month','year','event_validex') VALUES ('".$town."','". date('m')."','". date('Y')."','1')";
$result = $this->tep_db_query($query);
}
....
}
If I've misunderstood something, please let me know, happy to work through it with you.
Hope this helps. :)
精彩评论