I coded a function that creates queries and executes them from $_POST but it’s really not self explanatory and I want to code something simpler and easier to understand.
So I’m gonna try to do that right now. If you have something you have used before or have some suggestion on how to go about this please let me know.
Here is the function that I have (this function is inside the Form class)
public function insertAction($tables, $connectingColumn=''){
if(!is_array($tables)){
throw new Exception('The tables parameter must be an array, even if only one table will be used.');
}
$columns = array();
$queries = array();
foreach($tables as $key => $value){
$query_data = '';
$result = mysql_query('SHOW COLUMNS FROM '.$value);
if(!$result){
throw new Exception('Query to find column names failed.');
}
if(mysql_num_rows($result) > 0){
while($row = mysql_fetch_assoc($result)){
/* echo '<pre>';
print_r($row);
echo '</pre>';
echo '<pre>';
print_r($this->_submitted);
echo '</pre>'; */
if(array_key_exists($row['Field'], $this->_submitted)){
if($row['Field']=='password'){
$query_data .= $row['Field'].'="'.mysql_real_escape_string(md5($_POST[$row['Field']])).'", ';
}else{
$query_data .= $row['Field'].'="'.mysql_real_escape_string($_POST[$row['Field']]).'", ';
}
}
$columns[$key][] = $row['Field'];
}
}
$query_data = substr($query_data, 0, -2);
$queries[] = 'INSERT INTO '.$tables[$key].' SET '.$query_data;
}
if($_SERVER['REQUEST_METHOD']=='POST'){
if(count($this->_errors)==0){
echo '<pre>';
print_r($queries);
echo '</pre>';
$first = true;
foreach($queries as $value){
if($first){
// make first query
if(!mysql_query($value)){
throw new Exception('Error trying to execute first query.');
echo 'Query error: '.mysql_error();
}else{
echo '<p>new record inserted</p>';
}
$last_inserted_id = mysql_insert_id();
$first = false;
}else{
// make query and use mysql_insert_id to insert into column that connects the tables, in this case it would be userID
$value .= ', '.$connectingColumn.'="'.$last_inserted_id.'"';
if(!mysql_query($value)){
throw new Exception('Error trying to execute query.');
}
}
}
}
}
}