multiple inserts into different tables

[razorlegacy]

*img below*

I want to list these variables up to 50 then insert the variables into the table. EXACTLY how should I write the code...

 

Table code**

CREATE TABLE `questions` (
  `qid` int(11) NOT NULL auto_increment,
  `test_id` int( NOT NULL default '0',
  `question` text NOT NULL,
  `img` varchar(255) NOT NULL default '',
  `a1` text NOT NULL,
  `a2` text NOT NULL,
  `a3` text NOT NULL,
  `a4` text NOT NULL,
  `a5` text NOT NULL,
  `c_a` tinyint(11) NOT NULL default '0',
  `c_a_img` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`qid`),
  KEY `test_id` (`test_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 

I need the exact code to insert into DB all the values for each question#.

ex.

question 1 will insert all in that block

 

then

 

question 2

 

so on...

 

Thank you

[trq]

I need the exact code to insert into DB all the values for each question

 

Then your in the wrong forum. We don't provide people with code, we help YOU fix YOUR coding problems.

 

Either post the code you have and a description of what is not working, or post for a programmer in our freelance board.

[razorlegacy]

K

This is what I have

 

$test_id = check_input($_POST['test_id']);
$question = check_input($_POST['question']);
$a1 = check_input($_POST['a1']);
$a2 = check_input($_POST['a2']);
$a3 = check_input($_POST['a3']);
$a4 = check_input($_POST['a4']);
$a5 = check_input($_POST['a5']);
$c_a = check_input($_POST['c_a']);

if(!empty($_POST['img'])){ $img = check_input($_POST['img']); }else{ $img = "NULL"; }

$sql = @mysql_query("INSERT INTO `questions` (`qid`, `test_id`, `question`, `img`, `a1`, `a2`, `a3`, `a4`, `a5`, `c_a`, `c_a_img`) VALUES (NULL, '$test_id', '$question', '$img', '$a1', '$a2', '$a3', '$a4', '$a5', '$c_a', '$c_a_img')");
if(!$sql){
      echo "Error inserting your information into MySQL: ".mysql_error();
      footer();
      exit();
}

 

It doesn't loop the input into the DB

 

Just does #1 insert into DB and then stops

 

 

Help, I want to insert all 50 by block/Question#

[Fergusfer]

It doesn't loop the input into the DB

Just does #1 insert into DB and then stops 

Help, I want to insert all 50 by block/Question#

 

I'm not clear on your question.  I see nothing in your posts that relates to inserting into different tables (which is your topic).

 

You comment that it doesn't "loop" the input.  Well, no, it doesn't.  There is no loop in your code.  I'm not sure how you would expect it to do that or what the point would be without using an array or other multi-value dataset for the input, which you are not.

 

It looks to me as though you are structuring your database as one quiz per row, but then you seem to complain that it only inserts one row.  You have a field labelled "qid".  Perhaps this is quiz ID?  Maybe this is the table's primary key?  Perhaps it is using auto_increment?  If so, you should omit it from the INSERT or set its value to DEFAULT, not NULL.  I don't think MySQL is likely very happy with passing NULL as the value of an auto_increment ID.  I've never tried it, but I don't think it would work.  It's certainly not proper.

[btherl]

Can you show us your form as well?

 

Or an extract of it at least (2 questions minimum, and the opening form tag).

[razorlegacy]

<form id="insert" name="insert" method="post" action="questions.php">
Select a Test <select name="test_id">
<?
$sql = mysql_query("SELECT * FROM tests ORDER BY version ASC");
$tests = array();
while ($test = mysql_fetch_assoc($sql)) {
$tests[] = sprintf("<option value=\"%s\">%s->%s-%s</option>\n", 
$test['test_id'], $test['version'], $test['date_m'], $test['date_y']); } echo '' . join('' , $tests);
?>
</select>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<? $y = 1; while($y < 6) { ?>
<tr>
<td width="10" align="right" valign="top"><b><?php echo $y; ?>)</b></td>
<td>Questions<br/>
<textarea name="question" cols="60" rows="2"></textarea><br/>
Image<br/>
<input name="img" type="text" size="40" maxlength="50"/><br/><br/>
<table width="95%" border="0" cellspacing="0" cellpadding="5">
  <tr>
    <td colspan="3"><strong>Answers</strong></td>
    </tr>
  <tr>
    <td><strong>#</strong></td>
    <td align="center"><strong>CA</strong></td>
    <td><strong>Answer Text </strong></td>
  </tr>
<? $a = 1; while($a < 6) { ?>
  <tr>
    <td width="10" valign="middle"><b><?php echo $a; ?>)</b></td>
    <td width="10" align="center" valign="middle"><input name="c_a" type="radio" value="<?= $a; ?>"/></td>
    <td valign="top"><textarea name="a<?= $a; ?>" cols="50" rows="2"></textarea></td>
  </tr>
<? $a++; } ?>
</table>
</td></tr>
<? $y++; } ?>
</table>
</form>

 

qid = question id which is a auto increment

 

I want each question to have a row and they will all have the same test_id from the form

 

So how can I then insert with an array???

[Fergusfer]

So how can I then insert with an array???

 

You need to INSERT the data from the array by iterating it.  Example:

 

<?php

// assuming each of these values are previously defined constants
$database = new mysqli(HOST, USER, PASSWORD, DATABASE);
$array = array(1, 2, 3, 4, 5, 6);

$query = $database->prepare("INSERT INTO table VALUES (?)");
$query->bind_param('i', $item);

foreach ($array as $item) {
  $query->execute();
}

?>

[razorlegacy]

ok that is the hardcore programming

 

1,2,3,4,5 is each variable written out??

 

$query = $database->prepare("INSERT INTO table VALUES (?)");

Question mark is what I should put?? or insert something else??

[Fergusfer]

ok that is the hardcore programming

 

1,2,3,4,5 is each variable written out??

 

As you say.  Obviously your data will be more interesting than 1, 2, 3, 4, 5, 6; but naturally, I am not trying to code your application for you.  I'm just trying to explain concepts and approaches.

 

The statement:

 

$array = array(1, 2, 3, 4, 5, 6);

 

creates an enumerated array with those elements.  So $array[0] is equal to 1, and $array[3] is equal to 4.  You can extend this to create associative arrays, multi-dimensional arrays (requiring nested foreach() loops) and so on.

 

$query = $database->prepare("INSERT INTO table VALUES (?)");

Question mark is what I should put?? or insert something else??

 

This is an example of a prepared query using the mysqli PHP extension (preferred replacement for the mysql extension).  The question mark is like an insertion point controlled by the bind_param() method call.

 

This:

 

$query->bind_param('i', $item);

 

means treat the value of $item as an integer and when execute() is called, replace the first question mark with the value of $item.  This is a great (and faster) way to interact with MySQL when the query will be called repeated in a loop.

 

It's very elegant that foreach() can be combined to iteratively update the $item variable so that the body of the foreach() need only be the execute() method call.

[razorlegacy]

I am receiving an error of

Fatal error: Cannot instantiate non-existent class: mysql in question.php on line 27

at

$database = new mysqli(localhost, gainfina, ******, gainfina_nuke1);

 

My entire code is

$database = new mysqli(localhost, gainfina, ******, gainfina_nuke1);
$array = array(NULL, '$test_id', '$question', '$img', '$a1', '$a2', '$a3', '$a4', '$a5', '$c_a', '$c_a_img');

$query = $database->prepare("INSERT INTO questions VALUES (?)");
$query->bind_param('i', $item);

foreach ($array as $item) {
  $query->execute();
}

[razorlegacy]

bump