List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 13 1999 9:12pm
Subject:Re: Script syntax
View as plain text  
On Mon, 1999-09-13 14:28:17 -0600, Michael Adams wrote:
[PHP code:]
> $result  =  mysql_query("SELECT master.lastname, master.firstname,  
> master.emplid, master.deptname, master.jobtitle, master.phone FROM  
> master LEFT JOIN main USING(emplid) WHERE main.emplid IS NULL");
[...]
>     while  ($row  =  mysql_fetch_array($result))  {
> 	mysql_query("INSERT INTO main (lastname, firstname, emplid,  
> dept, jobtitle, phone) VALUES  
> ($row[0],$row[1],$row[2],$row[3],$row[4],$row[5])");
> 	}
> 
> --the above insert statement doesn't work.

As you don't check the return code of mysql_query, it's hard to see
_what_ the error is ...  Maybe the db user which you used in the
mysql_connect doesn't have the INSERT privilege on the table main?

And, you must put strings into quotes!  I assume all fields except
emplid are string fields?  Then try the following.  Using AddSlashes
puts us on the safe side if the fields may contain Apostrophes or
Backslashes (instead you also could set magic_quotes_runtime):

  while  ($row  =  mysql_fetch_array($result))  {
    $query = sprintf(
              "INSERT INTO main
                 (lastname, firstname, emplid, dept, jobtitle, phone)
               VALUES
                 ('%s'    , '%s'     , %s    , '%s', '%s'    ,'%s')",
               AddSlashes($row[0]), AddSlashes($row[1]),
               AddSlashes($row[2]), AddSlashes($row[3]),
               AddSlashes($row[4]),  AddSlashes($row[5]) );
    mysql_query($query);
  }

> With the while statement in place, the selection takes place but
> nothing is inserted and the results are not displayed.

"Nothing inserted", because the insert probably failed.  No results
displayed, because you already fetched all result rows from the first
query in this while loop!

The most easy solution is to combine the two loops into one:

  $query = "SELECT m.lastname, m.firstname, m.emplid, m.deptname,
                   m.jobtitle, m.phone
             FROM  master AS m LEFT JOIN main USING(emplid)
             WHERE main.emplid IS NULL";
  $result = mysql_query($query)
     or die("Invalid query $query!");
  $entry = mysql_num_rows($result);
  if (0 == $entry) {
    echo "No names found!<P>\n";
  }
  else {
    echo "<H2 ALIGN=center>Here are the names you requested</H2>\n";
    echo "<H4 ALIGN=center>$entry found</H4>\n<P><TABLE>\n";
    while  ($row  =  mysql_fetch_array($result))  {
      // 1st, show row contents.
      printf("<TR><TD>%s,
%s</TD><TD>%s</TD><TD>%s</TD><TD>%s</TD><TD>%s</TD></TR>\n",
             HtmlSpecialChars($row[0]), HtmlSpecialChars($row[1]),
             HtmlSpecialChars($row[2]), HtmlSpecialChars($row[3]),
             HtmlSpecialChars($row[4]), HtmlSpecialChars($row[5]) );
      // 2nd, insert it into main.
      $query = sprintf(
                "INSERT INTO main
                   (lastname, firstname, emplid, dept, jobtitle, phone)
                 VALUES
                   ('%s'    , '%s'     , %s    , '%s', '%s'    ,'%s')",
                 AddSlashes($row[0]), AddSlashes($row[1]),
                 AddSlashes($row[2]), AddSlashes($row[3]),
                 AddSlashes($row[4]),  AddSlashes($row[5]) );
      mysql_query($query)
        or die("Invalid query $query!");
    }
    echo "</TABLE></P>\n";
  }

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Script syntaxMichael Adams14 Sep
  • Re: Script syntaxMartin Ramsch14 Sep