looped insert [Solved]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Actually, looking closer at your code, it appears that you are inserting the same data multiple times. $s_id, $q_no,$r_text, and $r_radio are only set once, but you are storing them in the database multiple times. You'll want to clear that up before going any further.

An example of how to implement what I was saying is:
Your way:

Code: Select all

while($row = mysql_fetch_assoc($result))
   {
     $query = "INSERT INTO my_table VALUES ('$rowїcol1]','$rowїcol2']);
     $insert_result = mysql_query($query);
   }
My way:

Code: Select all

while($row = mysql_fetch_assoc($result))
   {
     $value_clause .= "('$rowїcol1]','$rowїcol2]'),";
   }
   $value_clause = rtrim(',',$value_clause);

   $query = "INSERT INTO my_table VALUES $value_clause";

   mysql_query($query);
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post by C_Calav »

hi guys,

thanx for your help pickle and feyd i have looked at your example code alot and i think its a bit over my head :S

i have decided to try it like this (not sure if it can even work) but we will see or someone will maybe tell me!

Code: Select all

<?php
     session_start(); 
     ob_start();
     
	include("conection.php"); 
     

	if ($_SERVER&#1111;'REQUEST_METHOD'] == "POST") 
        &#123;

	$s_id = $_POST&#1111;"s_id"];

	$sql="SELECT MAX (q_no) FROM tbl_questions where s_id='$s_id'";
	$result = mysql_query($sql) or die ("Execution failed: ".mysql_error());

	for ($i=0; $i <$result; $i++)
  	&#123;
	$q_no = $_POST&#1111;"q_$i"];
	$r_text = $_POST&#1111;"txt_answer_$i"];
	$r_radio = $_POST&#1111;"radio_$i"];

	$sql1  = "INSERT INTO tbl_results (s_id,q_no,r_text,r_radio) VALUES ('$s_title','$s_intro','$s_footer')";
	$result1 = mysql_query($sql) or die ("Execution failed: ".mysql_error());
	&#125;

	&#125; 

?>
getting this error.
Execution failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(q_no) FROM tbl_questions where s_id='47'' at line 1
is it because of this?

can this be posible

Code: Select all

for ($i=0; $i <$result; $i++)

$q_no = $_POST&#1111;"q_$i"];
$r_text = $_POST&#1111;"txt_answer_$i"];
$r_radio = $_POST&#1111;"radio_$i"];
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it's a problem with the SELECT, your guess happens after the select is run.
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post by C_Calav »

can you help me with the sql feyd?

i followed this example

SELECT MAX (expression )
FROM tables
WHERE predicates;

i cahnged it to suit me

$sql="SELECT MAX (q_no) FROM tbl_questions where s_id='$s_id'";

thanx
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

well.. it says (q_no) is the start of the error... so I'd start playing with the query in phpMyAdmin around that point.
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post by C_Calav »

i cant see what is wrong!

is the sql syntax is correct?
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post by C_Calav »

ok thinkj i got it now

SELECT MAX(q_no) FROM tbl_question;

i think i had a space between MAX and (q_no) that was throwing it out
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post by C_Calav »

fixed.. the coloums didnt match up.. my bad
Last edited by C_Calav on Mon Feb 14, 2005 4:44 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

trying to insert 4 fields, with only 3 values... :roll:
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post by C_Calav »

ok

here is my out put,
sql: SELECT MAX( q_no ) FROM tbl_question WHERE s_id = '47'
result is: Resource id #4
q_max: Array
Execution failed2: Column count doesn't match value count at row 1
how come i am getting this:
q_max: Array
??

the sql in phpmyadmin tells me the out put is- 11

and here is my code.

Code: Select all

$sql="SELECT MAX( q_no ) FROM tbl_question WHERE s_id = '$s_id'";  

	$result = mysql_query($sql) or die ("Execution failed1: ".mysql_error()); 
	
	$q_max = mysql_fetch_row($result); 

	echo"sql: $sql <br />";
	echo"result is: $result <br />"; 
	echo"q_max: $q_max <br />";

	for ($i=0; $i <$q_max; $i++) 
	&#123; 
	$q_no = $_POST&#1111;"q_$i"]; 
	$r_text = $_POST&#1111;"txt_answer_$i"]; 
	$r_radio = $_POST&#1111;"radio_$i"]; 

	$sql1  = "INSERT INTO tbl_results (s_id,q_no,r_text,r_radio) VALUES ('$q_no','$r_text','$r_radio')"; 
	$result1 = mysql_query($sql1) or die ("Execution failed2: ".mysql_error());
Last edited by C_Calav on Mon Feb 14, 2005 5:02 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

count the commas in your field list, count the commas in your values list of the insert.
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post by C_Calav »

ok thanx feyd
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post by C_Calav »

yip that did it feyd.

gave me my looped insert but the loop carryied on about a 40 times then gave me

Fatal error: Maximum execution time of 30 seconds exceeded in C:\Apache2\htdocs\survey\survey_form_insert.php on line 30

looks like the loop is wrong,

i know it is to do with this

for ($i=0; $i <$q_max; $i++)

on my output q_max is

q_max: Array

when it should be a number.

whats going on here?

thanx
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

$q_max = mysql_fetch_row($result);

you need : (next time read the fine manual rtfm)

Code: Select all

$row = mysql_fetch_row($result);
$q_max = $row&#1111;0];
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post by C_Calav »

thanx timvw

what is rtfm?
Post Reply