I have two forms - the first shows a list of advertisers, with the options to Add New (goes to the formsc.php) for a totally new advertiser, and edit or delete based on the advertiser id. When I click on add new and try to insert data I get the following error

INSERT INTO advertisers (category_id,advertiser_name,sort_name,img1,img1width,img1height,img2,img2width,img2height,VISIBLE) VALUES (1,'The new thing','new thing','images/7616.jpg',300,400,'','','',1)


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /hermes/web09/b2871/pow.capegazette/htdocs/formSC.php on line 45

Also, when I click on edit, no data is displayed in the formsc.php to edit.

Here's the formsc code - please help me figure this out. I can also post the table structure if needed.

<?php
ini_set('display_errors',1);
error_reporting(E_ALL ^ E_NOTICE);
require_once("includes/connection.php");
$TYPE_COLUMN = 0;
$LABEL_COLUMN = 1;
$FIELD_COLUMN = 2;
$VALUE_COLUMN = 3;
$ADVERTISERS_OBJECTS = array("category_id" => array("checkbox", "Category", "category_id", "Antique and Auction,Automobile,Construction and Services,Entertainment / Movie / Video,Finance,
Food and Drink,Health / Fitness / Beauty,Home Products / Services,Lawn and Garden,Local Events,Medical,Real Estate,Religion,Restaurants,Retail"),
                        "name" => array("textfield", "Advertiser Name", "advertiser_name", ""),
                        "sortname" => array("textfield", "Sort Name", "sort_name", ""),
                        "image1" => array("textfield", "Image 1 (URL)", "img1", ""),
                        "img1width" => array("textfield", "Image 1 Width", "img1width", ""),
                        "img1height" => array("textfield", "Image 1 Height", "img1height", ""),
                        "img2" => array("textarea", "Image 2 (URL)", "img2", ""),
                        "img2width" => array("textfield", "Image 2 Width", "img2width", ""),
                        "img2height" => array("textfield", "Image 2 Height", "img2height", ""),
                        "visible" => array("radio", "Visible", "visible", "Visible, Not Visible")
                        );


$METHOD_TABLE = array("NAME" => array("onBlur" => "fillsortname()"));
$rec = array();
$mode = $_POST["mode"];
switch ($mode) {
  case "delete":
  case "edit":
    $query = sprintf("SELECT * FROM advertisers WHERE advertiser_id=%s", $_REQUEST["advertiser_id"]);
    $r = mysql_query($query);
    if($r){
        $rec = mysql_fetch_array($r, MYSQL_ASSOC);
    }else{
        print '<pre>Query: "'.$query.'"</pre>';
        print '<pre>'.mysql_error().'</pre>';
    }  
    break;

  case "insert":
//    printf("<div align=\"center\"><a href=\"manageSC.php\">Showcase Management</a></div><p>");
    printf("%s<p>\n", write_insert($_POST));
//    $result = mysql_query(write_insert($_POST));
//    $_POST["advertiser_id"] = mysql_insert_id($result);
    $result = mysql_query(sprintf("%s; select advertiser_id=@@IDENTITY", write_insert($_POST)));
	$tmprec = mysql_fetch_array($result, MYSQL_ASSOC);
	$_POST["advertiser_id"] = $tmprec["advertiser_id"];
    break;

  case "update":
//    printf("<div align=\"center\"><a href=\"manageSC.php\">Showcase Management</a></div><p>");
    printf("%s<p>\n", write_update($_POST));
    $result = mysql_query(write_update($_POST));
    break;
    
  case "confirm":
//    printf("<div align=\"center\"><a href=\"manageSC.php\">Showcase Management</a></div><p>");
    $result = mysql_query(sprintf("DELETE FROM advertisers WHERE advertiser_id = %d", $_POST["advertiser_id"]));
    break; 
    
  default:
    $mode = "add";
}


?>
<html>
<head>
<?php
write_java_functions();
?>
</head>
<body>
<?php
printf("<div align=\"center\"><a href=\"manageSC.php\">Showcase Management</a></div><p>");

if (!in_array($mode, array("insert", "update", "confirm"))) {
  switch ($mode) {
     case "delete":
       $mode = "confirm";
       break;
     case "edit":
       $mode = "update";
       break;
     case "add":
       $mode = "insert";
       break;
  }
  draw_advertiser_form($rec, $mode);
} else {
  if (is_bool($result) && !$result) {
?>
There was an error processing your request:
<?php
  } else {
?>
Record updated!
<?php
  }
}
?>
</body>
</html>
<?php
function write_java_functions() {
  global $ADVERTISERS_OBJECTS, $TYPE_COLUMN, $LABEL_COLUMN, $FIELD_COLUMN, $VALUE_COLUMN;
?>
<script language="javascript">
<?php
  foreach ($ADVERTISERS_OBJECTS as $key => $data) {
    switch($data[$TYPE_COLUMN]) {
      case "checkbox":
        printf("function update%s() {\n", $data[$FIELD_COLUMN]);      
        print("  form = window.document.advertiser_form;\n");
        $items = explode(",", $data[$VALUE_COLUMN]);
        if (count($items)) {
          printf("  form.%s.value = 0;\n", $data[$FIELD_COLUMN]);
          $val = 0;
          foreach ($items as $item) {
            printf("  if (form.%s_%d.checked) {\n", $data[$FIELD_COLUMN], $val);
            printf("    form.%s.value = eval(form.%s.value) + eval(form.%s_%d.value);\n", $data[$FIELD_COLUMN], $data[$FIELD_COLUMN], $data[$FIELD_COLUMN], $val);
            print("  }\n\n");
            $val++;
          }
        }
        print("}\n\n");
        break;
    }
  } 
  print("function verifyData() {\n");
  print("  form = window.document.advertiser_form;\n");
  foreach ($ADVERTISERS_OBJECTS as $key => $data) {
    switch($data[$TYPE_COLUMN]) {
      case "checkbox":
        printf("  update%s();\n", $data[$FIELD_COLUMN]);      
    }
  }
  print("  fillsortname();\n");
  print("  form.submit();\n");
  print("}\n\n");
  
?>


function fillsortname() {
  form = window.document.advertiser_form;
  if (form.SORT_NAME.value == "") {
    form.SORT_NAME.value = form.NAME.value.toUpperCase();
  }
}

</script>
<?php  
}


function write_insert($post) {
  global $ADVERTISERS_OBJECTS, $TYPE_COLUMN, $LABEL_COLUMN, $FIELD_COLUMN, $VALUE_COLUMN;
  
  $sql = "INSERT INTO advertisers (%s) VALUES (%s)";
  $fields = "";
  $values = "";
  foreach ($ADVERTISERS_OBJECTS as $key => $data) {
    if (in_array($data[$FIELD_COLUMN], array_keys($post))) { 
      $fields .= $data[$FIELD_COLUMN] . ",";
      if (!is_numeric($post[$data[$FIELD_COLUMN]])) {
        $values .= sprintf("'%s',", str_replace("'", "''", stripslashes($post[$data[$FIELD_COLUMN]])));
      } else {
        $values .= $post[$data[$FIELD_COLUMN]] . ",";
      }
    }    
  }
  if ($len = strlen($fields)) {
    $fields = substr($fields, 0, $len - 1);
  }
  if ($len = strlen($values)) {
    $values = substr($values, 0, $len - 1);
  }
  
  return sprintf($sql, $fields, $values);
}

function write_update($post) {
  global $ADVERTISERS_OBJECTS, $TYPE_COLUMN, $LABEL_COLUMN, $FIELD_COLUMN, $VALUE_COLUMN;

  $sql = "UPDATE advertisers SET %s WHERE advertiser_id=%d";
  $fields = "";

  foreach ($ADVERTISERS_OBJECTS as $key => $data) {
    if (in_array($data[$FIELD_COLUMN], array_keys($post))) { 
      $fields .= $data[$FIELD_COLUMN] . " = ";
      if (!is_numeric($post[$data[$FIELD_COLUMN]])) {
//        $fields .= sprintf("\'%s\',", str_replace("'", "''", $post[$data[$FIELD_COLUMN]]));
        $fields .= sprintf("'%s',", str_replace("'", "''", stripslashes($post[$data[$FIELD_COLUMN]])));
      } else {
        $fields .= $post[$data[$FIELD_COLUMN]] . ",";
      }
    }    
  }
  if ($len = strlen($fields)) {
    $fields = substr($fields, 0, $len - 1);
  }
  return sprintf($sql, $fields, $post["advertiser_id"]);

}

function write_delete($post) {
  global $ADVERTISERS_OBJECTS, $TYPE_COLUMN, $LABEL_COLUMN, $FIELD_COLUMN, $VALUE_COLUMN;
  
  $sql = "DELETE advertisers WHERE advertiser_id=%d";
  return sprintf($sql, $post["advertiser_id"]);
}


function draw_advertiser_form($rec, $mode) {
  global $ADVERTISERS_OBJECTS, $TYPE_COLUMN, $LABEL_COLUMN, $FIELD_COLUMN, $VALUE_COLUMN;
?>
<form name="advertiser_form" method="post">
<input type="hidden" name="mode" value="<?php echo $mode ?>">
<table width="75%" style="border: 2px solid black" align="center">
<tr style="background-color: black; color: white; font-weight:bold; font-size:14pt">
<td colspan="2" align="center">
ADVERTISER INFORMATION
</td>
</tr>
<?php  
  foreach ($ADVERTISERS_OBJECTS as $key => $data) {
?>
<tr style="border: 2px solid black">
<td align="right" valign="top"><?php echo $data[$LABEL_COLUMN] ?></td>
<td>
<?php
    draw_object($data, $rec);
?>
</td>
</tr>

<?php    
  }
?>
<tr style="border: 2px solid black">
<td colspan="2" align="center">
<input type="submit" value="<?php echo $mode == "confirm" ? "Confirm Delete" : "Save" ?>" onClick="verifyData()">
</td>
</tr>
</table>
<?php
  if ($rec["advertiser_id"] > 0) {
?>
<input type="hidden" name="advertiser_id" value="<?php echo $rec["advertiser_id"] ?>">
<?php
  }
?>
</form>
<?php
}

function draw_object($data, $rec) {
  global $ADVERTISERS_OBJECTS, $TYPE_COLUMN, $LABEL_COLUMN, $FIELD_COLUMN, $VALUE_COLUMN;
    switch($data[$TYPE_COLUMN]) {
      case "radio":
        $items = explode(",", $data[$VALUE_COLUMN]);
        $val = 1;
        foreach ($items as $item) {
?>                     
<input type="<?php echo $data[$TYPE_COLUMN] ?>" name="<?php echo $data[$FIELD_COLUMN] ?>" value="<?php echo $val ?>" <?php echo write_field_methods($data[$FIELD_COLUMN]) ?><?php echo ($rec[$data[$FIELD_COLUMN]] == $val) ? " checked" : "" ?>><?php echo $item ?><br>
<?php
          $val++;
        }
        break;

      case "checkbox":
        $items = explode(",", $data[$VALUE_COLUMN]);
?>
<input type="hidden" name="<?php echo $data[$FIELD_COLUMN] ?>" value="0">
<?php      
        $val = 0;
        foreach ($items as $item) {
          $fieldname = $data[$FIELD_COLUMN] . "_" . $val;
          $bit = pow(2,$val++);
?>                     
<input type="<?php echo $data[$TYPE_COLUMN] ?>" name="<?php echo $fieldname ?>" value="<?php echo $bit ?>" onClick="update<?php echo $data[$FIELD_COLUMN] ?>()" <?php echo write_field_methods($data[$FIELD_COLUMN]) ?> <?php echo (($rec[$data[$FIELD_COLUMN]] & $bit) == $bit) ? " checked" : "" ?>><?php echo $item ?><br>
<?php
        }
        break;

      case "textarea":
?>                     
<textarea name="<?php echo $data[$FIELD_COLUMN] ?>" rows="5" cols="60" <?php echo write_field_methods($data[$FIELD_COLUMN]) ?>><?php echo $rec[$data[$FIELD_COLUMN]] ?></textarea><p>
<?php
        break;

      case "textfield":
?>                     
<input type="<?php echo $data[$TYPE_COLUMN] ?>" name="<?php echo $data[$FIELD_COLUMN] ?>" size="60" value="<?php echo $rec[$data[$FIELD_COLUMN]] ?>" <?php echo write_field_methods($data[$FIELD_COLUMN]) ?>><br>
<?php
        break;
        
      default:
?>                     
<input type="<?php echo $data[$TYPE_COLUMN] ?>" name="<?php echo $data[$FIELD_COLUMN] ?>" value="<?php echo $rec[$data[$FIELD_COLUMN]] ?>" <?php write_field_methods($data[$FIELD_COLUMN]) ?>><br>
<?php
    }
}

function write_field_methods($field) {
  global $METHOD_TABLE;
  
  $ret = "";
  if (in_array($field, array_keys($METHOD_TABLE)) && ($methods = $METHOD_TABLE[$field])) {
    foreach ($methods as $evt => $code) {
      $ret .= sprintf("%s=\"%s\" ", $evt, $code);
    }
  }
  return $ret;
}
?>

Thanks.

I honestly don't want to dig through 216 lines of code to try and understand what you are trying to do with this line of code here
$result = mysql_query(sprintf("%s; select advertiser_id=@@IDENTITY", write_insert($_POST)));
but it's clearly not producing a mySql result for the next line to work with.

Maybe you could explain why you wrote you sql that way and what you expect it to look like to the mysql_query method?

Try

print sprintf("%s; select advertiser_id=@@IDENTITY", write_insert($_POST));
exit;

and if what you get in your browser is not sql syntax, fix it.

Cheers

This article has been dead for over six months. Start a new discussion instead.