Multiple MYSQL Update Function Help!

[vicodin]

Ok so i am looking to make a function that is capable of updating multiple MYSQL rows.

Not sure how i would do this. I know how to make one for a single update but having trouble with making a function do multiple row updates.

Example : Lets say i have row category and row category_name.

 

I want a function that will allow me to do this.

 

function UpdateDB($table,$rows,$updates,$where,$where_row_equals)

 

Pretty much when i execute it i would like to be able to do this...

UpdateDB("mytable","category,category_name","10,MyCatName","category","10");

 

Any help would be greatly appreciated.

 

P.S. Where i am having problems is the mysql syntax needs to be

"SET $row1 = $update1  $row2 = $update2" and so on.

 

[Philip]

I know you're wanting to make your own.... but have you tried mysqli? It has a multi query option.

 

http://php.net/mysqli

 

[vicodin]

Which one would i use... never used mysqli before.

[premiso]

implodeexplode

 

<?php
$col = "category,category_name";
$val = "10,mycatname";

$cols = explode($col, ",");
$vals = explode($val, ",");

$cnt = count($cols);

for ($i=0;$i<$cnt;$i++) {
      $updateSQL .= " " . $cols[$i] . " = " . $cols[$i] . ", ";
}

$updateSQL = substr($updateSQL, 0, -2); // not sure on this
// etc etc
?>

 

But if you ask me, due to that your values may include a comma I would pass each one as an array IE:

 

updatedb("mytable", array("catageory", "category_name"), array("10", "MyCatName"), ....etc

 

That would make it easier and allow you to pass just about anything into it and parse it without a problem.

[Philip]

Well, as long as your PHP version is up to date you can use it.

 

Connection:

<?php
$mysqli = new mysqli('localhost','user','pass','database');
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
?>

 

Multi-query:

<?php
$query = "SELECT * FROM `users` WHERE `id`='5';";
$query.= "SELECT * FROM `sometable` WHERE `live`='1' ";
// etc... just seperate the queries with a semicolon
if ($mysqli->multi_query($query)) {
    // DO - so that the first query gets done first...
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                echo $row[0];
            }
            // Free result
            $result->free();
        }
        // if there are more results, print a divider
        if ($mysqli->more_results()) {
            echo '-----------------<br />';
        }
    } while ($mysqli->next_result());
}
?>

[premiso]

Well, as long as your PHP version is up to date you can use it.

 

Connection:

<?php
$mysqli = new mysqli('localhost','user','pass','database');
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
?>

 

Multi-query:

<?php
$query = "SELECT * FROM `users` WHERE `id`='5';";
$query.= "SELECT * FROM `sometable` WHERE `live`='1' ";
// etc... just seperate the queries with a semicolon
if ($mysqli->multi_query($query)) {
    // DO - so that the first query gets done first...
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                echo $row[0];
            }
            // Free result
            $result->free();
        }
        // if there are more results, print a divider
        if ($mysqli->more_results()) {
            echo '-----------------<br />';
        }
    } while ($mysqli->next_result());
}
?>

 

I think his title is mis leading. I think he wants to be able to put in multiple rows/values and dynamically create an update statement.

 

Really it is not a multi-query as much as just a normal update statement created by values passed to a function.

[hobeau]

I know you're wanting to make your own.... but have you tried mysqli? It has a multi query option.

 

http://php.net/mysqli

 

 

PDO (PHP Data Objects http://us2.php.net/manual/en/book.pdo.php) is the preferred method for connecting to databases and is/has been slowly but surely replacing the older mysql/mysqli extensions. In PHP6 this is the default method for connecting to mysql/mssql/postgres/oracle and many more databases. Everyone should really think about looking into this and start to use it as the mysql extensions are soon to be faded out. PDO is much faster than the mysql/mysqli drivers and allows you to connect to many different databases with the exact same code. No more trying to figure out what functions to use to connect to mysql vs mssql etc... Also, it has features such as prepared queries and has more security than the previous function sets. Alot of work is going into phasing out mysql/mysqli so lets all get acquainted with it!!!