[SOLVED] IF/While loop and 2 Sql INSERT statements

[CodeMama]

I have two sql inserts, one table holds name and address fields, the other holds all other data associated with the name and address..I want to insert the name and address into one and then have an if statemet that says if the name and address exists enter the other data under the name and address ID , if not then enter the name and address in the one table and the other info in the other...does this make sense..and how do I do it?

 

my two sql statements:

    $sql = "INSERT INTO `restaurants` (name, address)  VALUES (";
    $sql .= " '$mysql_name', '$mysql_address')";
    mysql_query($sql);


    $sql = "INSERT INTO `inspections` (inDate, inType, notes, critical, cviolations, noncritical)  VALUES (";
    $sql .= " '$mysql_inDate', '$mysql_inType', '$mysql_notes', '$mysql_critical', '$mysql_cviolations', '$mysql_noncritical')";
    mysql_query($sql);

[therealwesfoster]

You would do something like this:

 

<?php
// First check if the name exists
$sql = mysql_query("SELECT * FROM `TABLE_WITH_NAME` WHERE `name`='{$the_name}'");
if (mysql_num_rows($sql) > 0){
    // This means the name exists.. UPDATE that table
}
else{
    // The name doesn't exists, add all the info
}
?>

[akitchin]

I have two sql inserts, one table holds name and address fields, the other holds all other data associated with the name and address..I want to insert the name and address into one and then have an if statemet that says if the name and address exists enter the other data under the name and address ID , if not then enter the name and address in the one table and the other info in the other...does this make sense..and how do I do it?

 

my two sql statements:

    $sql = "INSERT INTO `restaurants` (name, address)  VALUES (";
    $sql .= " '$mysql_name', '$mysql_address')";
    mysql_query($sql);


    $sql = "INSERT INTO `inspections` (inDate, inType, notes, critical, cviolations, noncritical)  VALUES (";
    $sql .= " '$mysql_inDate', '$mysql_inType', '$mysql_notes', '$mysql_critical', '$mysql_cviolations', '$mysql_noncritical')";
    mysql_query($sql);

 

if you've just inserted the name and address, doesn't it stand to reason that the name and address now exist in the database? or are you just collecting all the information in one form and are wondering how to check if the name and address from that form already exist?

 

if the latter, you may want to reconsider your design - someone might, for example, put in "Faye's Restaurant" and an inspection for her restaurant. someone else may then come in and enter an inspection for "Faye's restaurant," causing a duplicate entry because the string doesn't match exactly. it gets even more muddled with address, since everyone has a different way of typing the same address.

[CodeMama]

The final thing that worked was this:

 

// First check if the name exists
$sql ="SELECT * FROM `restaurants` WHERE `name`='{$mysql_name}' AND `address` ='{$mysql_address}'";
$result = mysql_query($sql) or die(mysql_error());

if (mysql_num_rows($result) > 0){
  
   $row = mysql_fetch_array($result);
   $ID = $row['ID'];
   
    // This means the name exists.. UPDATE inspections table
    $sql = "INSERT INTO `inspections` (ID, inDate, inType, notes, critical, cviolations, noncritical)  VALUES (";
    $sql .= "'$ID', '$mysql_inDate', '$mysql_inType', '$mysql_notes', '$mysql_critical', '$mysql_cviolations', '$mysql_noncritical')";
    
    
mysql_query($sql);
    
}
else{
    // The name doesn't exists, add all the info
      $sql = "INSERT INTO `restaurants` (name, address)  VALUES (";
      $sql .= " '$mysql_name', '$mysql_address')";
mysql_query($sql);
         $ID = mysql_insert_id();
         
    $sql = "INSERT INTO `inspections` (ID, inDate, inType, notes, critical, cviolations, noncritical)  VALUES (";
    $sql .= " '$ID', '$mysql_inDate', '$mysql_inType', '$mysql_notes', '$mysql_critical', '$mysql_cviolations', '$mysql_noncritical')";
mysql_query($sql);
}
    
    
    

[therealwesfoster]

The final thing that worked was this:

 

// First check if the name exists
$sql ="SELECT * FROM `restaurants` WHERE `name`='{$mysql_name}' AND `address` ='{$mysql_address}'";
$result = mysql_query($sql) or die(mysql_error());

if (mysql_num_rows($result) > 0){
  
   $row = mysql_fetch_array($result);
   $ID = $row['ID'];
   
    // This means the name exists.. UPDATE inspections table
    $sql = "INSERT INTO `inspections` (ID, inDate, inType, notes, critical, cviolations, noncritical)  VALUES (";
    $sql .= "'$ID', '$mysql_inDate', '$mysql_inType', '$mysql_notes', '$mysql_critical', '$mysql_cviolations', '$mysql_noncritical')";
    
    
mysql_query($sql);
    
}
else{
    // The name doesn't exists, add all the info
      $sql = "INSERT INTO `restaurants` (name, address)  VALUES (";
      $sql .= " '$mysql_name', '$mysql_address')";
mysql_query($sql);
         $ID = mysql_insert_id();
         
    $sql = "INSERT INTO `inspections` (ID, inDate, inType, notes, critical, cviolations, noncritical)  VALUES (";
    $sql .= " '$ID', '$mysql_inDate', '$mysql_inType', '$mysql_notes', '$mysql_critical', '$mysql_cviolations', '$mysql_noncritical')";
mysql_query($sql);
}
    
    
    

 

Glad I could help