#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Austin, TX, USA
    Posts
    11
    Rep Power
    0
    Anyone out there know of a good way to dynamically create an UPDATE statement such as:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    $query_upd = "UPDATE $table SET $values WHERE custid='$custid'";
    [/code]

    $values would be something like field1='$field1',field2='$field2', and so on.

    Code prior to this reads the DB structure and gets the table field names. Then I want to be able to update the values. I've experienced a problem dynamically creating this though because of some problem related to magic quotes I haven't been able to solve.

    If you have a good way of dynamically creating the queries and would like to share I'm game. Thank you.

    ------------------
    Dave Bryant
    dave@siliconhills.net
    dbryant@jump.net

    [This message has been edited by davebryant (edited August 03, 2000).]
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    Perth West Australia
    Posts
    757
    Rep Power
    15
    Just done exactly that yesterday! - just started working with other peoples tables & it a pain.

    you will need to rearrange how you connect to your db but this works.

    <?php $db = mysql_connect("localhost","virtual");
    mysql_select_db("$DATABASENAME",$db);?>
    <html>
    <body bgcolor="#ffffff">
    <?php $result = mysql_list_tables ("$DATABASENAME");$i=0;
    while ($i < mysql_num_rows ($result)) {
    $tb_names[$i] = mysql_tablename ($result, $i);$i++;
    }
    ?>
    <?php if(!$submit){$table="<i>not selected</i>";$xnum=0;}?>
    <center><form name=ff method=post action="sqlgen.php">
    <b>Select table</b>:<select name="table">
    <?php for($x=0;$x<$i;$x++){?>
    <option value="<?php echo $tb_names[$x];?>"><?php echo $tb_names[$x];?></option>
    <?php }?>
    </select>
    <input type="submit" name="submit" value="submit">
    </form>
    </center>

    <?php if($submit){
    $SQL="SELECT * FROM $table";
    $result = mysql_query($SQL,$db);
    $xnum = mysql_num_fields($result);

    }
    echo "<b><center>$table contains $xnum fields.<br><br></b></center>";?>

    <center>
    <table bgcolor=black><tr>
    <td bgcolor="#e3e3e3">Offset</td><td bgcolor="#e3e3e3">Field Name</td><td bgcolor="#e3e3e3">Field type</td></tr>

    <?php for($x=0;$x<$xnum;$x++){$name[$x]=mysql_field_name($result,$x);$type[$x]=mysql_field_type($result,$x);?>
    <tr>
    <td bgcolor="white"><?php echo $x;?></td>
    <td bgcolor="white"><?php echo $name[$x];?></td>
    <td bgcolor="white"><?php echo $type[$x];?></td>
    </tr>
    <?php }?>
    </table></center>

    <?php
    for($x=0;$x<count($name);$x++){
    $str .=$name[$x];$str .=",";
    $str2 .="'$"; $str2 .=$name[$x]; $str2 .="',";
    $sups .=$name[$x]; $sups .=" ='$"; $sups .=$name[$x]; $sups .="' , ";
    }
    $del=""DELETE FROM " .$table ." WHERE xxx = '$xxx'";

    $ins="$SQL="INSERT into " .$table;
    $ins .=" (" .$str .")" ." VALUES " ." (" .$str2 .");"" ;
    $ins=str_replace(",)",")",$ins);

    $ups="$SQL="UPDATE " .$table ." SET ";
    $ups .=$sups ."Z6Z " ." WHERE xxx ='$xxx'";";
    $ups=str_replace(", Z6Z ","",$ups);
    ?>
    <br><br>
    <center><table width=750 bgcolor=black wrap=virtual>
    <tr><td bgcolor="#e3e3e3">Insert</td><td bgcolor="white" width=700><font size=2 family=arial><?php echo $ins;?></font></td></tr>
    <tr><td bgcolor="#e3e3e3">Update</td><td bgcolor="white" width=700><font size=2 family=arial><?php echo $ups;?></font></td></tr>
    <tr><td bgcolor="#e3e3e3">Delete</td><td bgcolor="white" width=700><font size=2 family=arial><?php echo $del;?></font></td></tr>
    </table></center>
    </body>
    </html>


    Basically it creates 3 SQL query strings , 1 for UPDATE 1 for INSERT 1 for DELETE(ok the delete was a bit silly)

    Call the file sqlgen.php3 or rename the form action.



    ------------------
    Simon Wheeler
    FirePages -DHTML/PHP/MySQL
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Austin, TX, USA
    Posts
    11
    Rep Power
    0
    Thank you. This appears like it will do the trick. People, take notes from this guy, I know I am.

    ------------------
    Dave Bryant
    dave@siliconhills.net
    dbryant@jump.net

Similar Threads

  1. sql update inserts '0' and not var
    By SeenGee in forum PHP Development
    Replies: 2
    Last Post: February 16th, 2004, 10:18 PM
  2. Cant read sql queries from dump file
    By nukeu666 in forum MySQL Help
    Replies: 1
    Last Post: February 10th, 2004, 01:44 AM
  3. Database types UPDATE and DELETE queries
    By JBW in forum Database Management
    Replies: 0
    Last Post: January 21st, 2004, 03:40 AM
  4. Timer / SQL update trigger
    By JamieH in forum C Programming
    Replies: 4
    Last Post: December 22nd, 2003, 07:32 AM
  5. Using textarea to run SQL queries with php
    By OSX in forum PHP Development
    Replies: 4
    Last Post: December 18th, 2003, 06:37 PM

IMN logo majestic logo threadwatch logo seochat tools logo