我在我的Mysql数据库上有一个存储过程，但我需要更改它以通过PHP完成它。
我一直在这里寻找，我知道可以用PHP制作一个SP，但它需要Mysqli（我没有更新它，并且由于个人原因不能更新它）或者至少那是唯一的我发现这样做的方式（ 来源 ）。
所以对我来说只有Mysql，有可能从php做到吗？ 或者我应该将程序“翻译”为PHP格式？ 我的意思是不是创建一个过程而是一个函数（php函数），它实际上与PHP代码（循环，选择，调用等）相同，但不在DB上存储过程。
这是我的程序
DELIMITER // create procedure autor_products (in id int) begin update authors set authors_products= (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id = id) WHERE manufacturers_id = id; end // DELIMITER $$ CREATE PROCEDURE authors() BEGIN DECLARE a INT Default 0 ; DECLARE manu_length int; select max(manufacturers_id) into manu_length from authors; simple_loop: LOOP SET a=a+1; call autor_products(a); IF a >= manu_length THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END $$ DELIMITER ; commit;我设法做到这一点，这对于至少一次更新是“有效的”
<?php require('includes/configure.php'); //To get the DB name, user and pass require_once (DIR_FS_INC.'xtc_db_connect.inc.php'); //I use this to connect the DB $conn = xtc_db_connect() or die('Unable to connect to database server!'); $manu_lenght="select max(manufacturers_id) from authors;"; for ($i = 0;$i<=$manu_lenght;$i++){ //"for" not in use nor not working if i replace 1 for $i $update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id = 1) WHERE manufacturers_id = 1;"; } //will only update 1 author with authors_id = 1 so i needed to make a loop, but if i replace 1 for $i. it doesn't update at all $retval = mysql_query( $update, $conn ); if(! $retval ) { die('Could not update data: ' . mysql_error()); } echo "Updated data successfully\n"; ?>我如何使用$ i变量而不是特定的数字？ （所以它会因为循环而一次更新所有内容）
编辑：我的manu_lenght无法正常工作（它没有显示选择的结果），但我只是将那部分改成了一个数字，它仍然无法正常工作，有什么想法吗？
编辑2：为什么这段代码不起作用？ 如果只有$ a = 1，它就可以工作; 但不是如果我循环或制作$ a的一个小块。
$a = 1; do{ $update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id =$a) WHERE manufacturers_id =$a;"; echo $a; $a++; } while($a <= 10);我做错了什么？
I have a stored procedure on my Mysql DB, but i need to change it to get it done by PHP.
I've been looking around here and i know that is possible to make a SP from PHP, but it requires Mysqli (i don't have it updated, and updating it is not possible for personal reasons) or at least that was the only way i found to do it (source).
So for me that only have Mysql, it's possible to do it from php? or should i just "translate" the procedure into PHP format? I mean not making a procedure but a function instead (php function), that actually do the same with PHP code (loops, selects, calls and so) but not storing the procedure on the DB.
This is my procedure
DELIMITER // create procedure autor_products (in id int) begin update authors set authors_products= (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id = id) WHERE manufacturers_id = id; end // DELIMITER $$ CREATE PROCEDURE authors() BEGIN DECLARE a INT Default 0 ; DECLARE manu_length int; select max(manufacturers_id) into manu_length from authors; simple_loop: LOOP SET a=a+1; call autor_products(a); IF a >= manu_length THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END $$ DELIMITER ; commit;I managed to do this, which is "working" for at least one update
<?php require('includes/configure.php'); //To get the DB name, user and pass require_once (DIR_FS_INC.'xtc_db_connect.inc.php'); //I use this to connect the DB $conn = xtc_db_connect() or die('Unable to connect to database server!'); $manu_lenght="select max(manufacturers_id) from authors;"; for ($i = 0;$i<=$manu_lenght;$i++){ //"for" not in use nor not working if i replace 1 for $i $update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id = 1) WHERE manufacturers_id = 1;"; } //will only update 1 author with authors_id = 1 so i needed to make a loop, but if i replace 1 for $i. it doesn't update at all $retval = mysql_query( $update, $conn ); if(! $retval ) { die('Could not update data: ' . mysql_error()); } echo "Updated data successfully\n"; ?>How may i use the $i variable instead of a specific number? (so it would update all at once because of the loop)
Edit: My manu_lenght is not working (it doesn't show the result of the select) but i just changed that part into a number and it's still not working, any thoughts?
Edit 2: Why is this code not working? It works if it's only $a=1; but not if i loop or make a bucle of $a.
$a = 1; do{ $update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id =$a) WHERE manufacturers_id =$a;"; echo $a; $a++; } while($a <= 10);What im doing wrong?
最满意答案
经过几次尝试，我得到了我需要的东西。
这是我的代码：
<?php require('includes/configure.php'); require_once (DIR_FS_INC.'xtc_db_connect.inc.php'); $conn = xtc_db_connect() or die('Unable to connect to database server!'); //getting the total amount of authors into $manu_lenght variable $manu_lenght = mysql_query("select max(manufacturers_id) as sum from authors;" ); if(!$manu_lenght){ die("DB query failed : " . mysql_error()); $sum = ""; } //needed to fetch the result of the select query while ( $result = mysql_fetch_assoc($manu_lenght)){ $sum = $result["sum"]; } do{ $author_update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id =$a) WHERE manufacturers_id =$a;"; $a++; //while was here, so that's why it wasn't working $ret_author = mysql_query( $author_update, $conn ); } while($a <= $sum); //now is correct, it sends the query now if(!$ret_author ) { die('Could not update data: ' . mysql_error()); } echo "Updated authors successfully\n"; ?>我基本上做错了是太早结束循环，而不是让mysql_query函数发送查询（在这种情况下更新）
我没有调用该过程，而是通过PHP完成了过程转换。 希望这对其他人有用。
After several tries i got what i need.
This is my code:
<?php require('includes/configure.php'); require_once (DIR_FS_INC.'xtc_db_connect.inc.php'); $conn = xtc_db_connect() or die('Unable to connect to database server!'); //getting the total amount of authors into $manu_lenght variable $manu_lenght = mysql_query("select max(manufacturers_id) as sum from authors;" ); if(!$manu_lenght){ die("DB query failed : " . mysql_error()); $sum = ""; } //needed to fetch the result of the select query while ( $result = mysql_fetch_assoc($manu_lenght)){ $sum = $result["sum"]; } do{ $author_update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id =$a) WHERE manufacturers_id =$a;"; $a++; //while was here, so that's why it wasn't working $ret_author = mysql_query( $author_update, $conn ); } while($a <= $sum); //now is correct, it sends the query now if(!$ret_author ) { die('Could not update data: ' . mysql_error()); } echo "Updated authors successfully\n"; ?>What i was basically doing wrong is ending the loop too early, and not letting the mysql_query function send the query (update in this case)
Instead of calling the procedure, i did the procedure translation through PHP. Hope this is useful for anyone else.
将Mysql过程转换为PHP代码(Convert Mysql procedure into PHP code)我在我的Mysql数据库上有一个存储过程，但我需要更改它以通过PHP完成它。
我一直在这里寻找，我知道可以用PHP制作一个SP，但它需要Mysqli（我没有更新它，并且由于个人原因不能更新它）或者至少那是唯一的我发现这样做的方式（ 来源 ）。
所以对我来说只有Mysql，有可能从php做到吗？ 或者我应该将程序“翻译”为PHP格式？ 我的意思是不是创建一个过程而是一个函数（php函数），它实际上与PHP代码（循环，选择，调用等）相同，但不在DB上存储过程。
这是我的程序
DELIMITER // create procedure autor_products (in id int) begin update authors set authors_products= (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id = id) WHERE manufacturers_id = id; end // DELIMITER $$ CREATE PROCEDURE authors() BEGIN DECLARE a INT Default 0 ; DECLARE manu_length int; select max(manufacturers_id) into manu_length from authors; simple_loop: LOOP SET a=a+1; call autor_products(a); IF a >= manu_length THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END $$ DELIMITER ; commit;我设法做到这一点，这对于至少一次更新是“有效的”
<?php require('includes/configure.php'); //To get the DB name, user and pass require_once (DIR_FS_INC.'xtc_db_connect.inc.php'); //I use this to connect the DB $conn = xtc_db_connect() or die('Unable to connect to database server!'); $manu_lenght="select max(manufacturers_id) from authors;"; for ($i = 0;$i<=$manu_lenght;$i++){ //"for" not in use nor not working if i replace 1 for $i $update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id = 1) WHERE manufacturers_id = 1;"; } //will only update 1 author with authors_id = 1 so i needed to make a loop, but if i replace 1 for $i. it doesn't update at all $retval = mysql_query( $update, $conn ); if(! $retval ) { die('Could not update data: ' . mysql_error()); } echo "Updated data successfully\n"; ?>我如何使用$ i变量而不是特定的数字？ （所以它会因为循环而一次更新所有内容）
编辑：我的manu_lenght无法正常工作（它没有显示选择的结果），但我只是将那部分改成了一个数字，它仍然无法正常工作，有什么想法吗？
编辑2：为什么这段代码不起作用？ 如果只有$ a = 1，它就可以工作; 但不是如果我循环或制作$ a的一个小块。
$a = 1; do{ $update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id =$a) WHERE manufacturers_id =$a;"; echo $a; $a++; } while($a <= 10);我做错了什么？
I have a stored procedure on my Mysql DB, but i need to change it to get it done by PHP.
I've been looking around here and i know that is possible to make a SP from PHP, but it requires Mysqli (i don't have it updated, and updating it is not possible for personal reasons) or at least that was the only way i found to do it (source).
So for me that only have Mysql, it's possible to do it from php? or should i just "translate" the procedure into PHP format? I mean not making a procedure but a function instead (php function), that actually do the same with PHP code (loops, selects, calls and so) but not storing the procedure on the DB.
This is my procedure
DELIMITER // create procedure autor_products (in id int) begin update authors set authors_products= (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id = id) WHERE manufacturers_id = id; end // DELIMITER $$ CREATE PROCEDURE authors() BEGIN DECLARE a INT Default 0 ; DECLARE manu_length int; select max(manufacturers_id) into manu_length from authors; simple_loop: LOOP SET a=a+1; call autor_products(a); IF a >= manu_length THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END $$ DELIMITER ; commit;I managed to do this, which is "working" for at least one update
<?php require('includes/configure.php'); //To get the DB name, user and pass require_once (DIR_FS_INC.'xtc_db_connect.inc.php'); //I use this to connect the DB $conn = xtc_db_connect() or die('Unable to connect to database server!'); $manu_lenght="select max(manufacturers_id) from authors;"; for ($i = 0;$i<=$manu_lenght;$i++){ //"for" not in use nor not working if i replace 1 for $i $update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id = 1) WHERE manufacturers_id = 1;"; } //will only update 1 author with authors_id = 1 so i needed to make a loop, but if i replace 1 for $i. it doesn't update at all $retval = mysql_query( $update, $conn ); if(! $retval ) { die('Could not update data: ' . mysql_error()); } echo "Updated data successfully\n"; ?>How may i use the $i variable instead of a specific number? (so it would update all at once because of the loop)
Edit: My manu_lenght is not working (it doesn't show the result of the select) but i just changed that part into a number and it's still not working, any thoughts?
Edit 2: Why is this code not working? It works if it's only $a=1; but not if i loop or make a bucle of $a.
$a = 1; do{ $update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id =$a) WHERE manufacturers_id =$a;"; echo $a; $a++; } while($a <= 10);What im doing wrong?
最满意答案
经过几次尝试，我得到了我需要的东西。
这是我的代码：
<?php require('includes/configure.php'); require_once (DIR_FS_INC.'xtc_db_connect.inc.php'); $conn = xtc_db_connect() or die('Unable to connect to database server!'); //getting the total amount of authors into $manu_lenght variable $manu_lenght = mysql_query("select max(manufacturers_id) as sum from authors;" ); if(!$manu_lenght){ die("DB query failed : " . mysql_error()); $sum = ""; } //needed to fetch the result of the select query while ( $result = mysql_fetch_assoc($manu_lenght)){ $sum = $result["sum"]; } do{ $author_update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id =$a) WHERE manufacturers_id =$a;"; $a++; //while was here, so that's why it wasn't working $ret_author = mysql_query( $author_update, $conn ); } while($a <= $sum); //now is correct, it sends the query now if(!$ret_author ) { die('Could not update data: ' . mysql_error()); } echo "Updated authors successfully\n"; ?>我基本上做错了是太早结束循环，而不是让mysql_query函数发送查询（在这种情况下更新）
我没有调用该过程，而是通过PHP完成了过程转换。 希望这对其他人有用。
After several tries i got what i need.
This is my code:
<?php require('includes/configure.php'); require_once (DIR_FS_INC.'xtc_db_connect.inc.php'); $conn = xtc_db_connect() or die('Unable to connect to database server!'); //getting the total amount of authors into $manu_lenght variable $manu_lenght = mysql_query("select max(manufacturers_id) as sum from authors;" ); if(!$manu_lenght){ die("DB query failed : " . mysql_error()); $sum = ""; } //needed to fetch the result of the select query while ( $result = mysql_fetch_assoc($manu_lenght)){ $sum = $result["sum"]; } do{ $author_update= " update authors set products_amount = (SELECT DISTINCT count(products_id) FROM products INNER JOIN authors_to_manufacturers as am ON am.manufacturers_id = products.manufacturers_id WHERE authors_id =$a) WHERE manufacturers_id =$a;"; $a++; //while was here, so that's why it wasn't working $ret_author = mysql_query( $author_update, $conn ); } while($a <= $sum); //now is correct, it sends the query now if(!$ret_author ) { die('Could not update data: ' . mysql_error()); } echo "Updated authors successfully\n"; ?>What i was basically doing wrong is ending the loop too early, and not letting the mysql_query function send the query (update in this case)
Instead of calling the procedure, i did the procedure translation through PHP. Hope this is useful for anyone else.
发布评论