PHP - Inserting Csv Records Into A Mysql Table

Hi I'm using this code to insert multiple records. The code executes but nothing is entered into my database. This usually happens when there's a mismatch in data types.   How do I ensure that description goes in as text which in sql is wrapped in single quotes, but also make sure the other variables go in as numeric.

 // an array items to insert
    $array = array( 'theid'   => $theid,
            'descr'      =>   $descr,
            'costperunit'    => $costperunit,
            'quantity'       => $quantity,
'costperlot'       => $costperlot
            );


    // begin the sql statement
    $sql1 = "INSERT INTO  descriptions (jobid, description, costperunit, quantity, costperlot) VALUES ";

    $it = new ArrayIterator( $array );


    // a new caching iterator gives us access to hasNext()
    $cit = new CachingIterator( $it );


    // loop over the array
    foreach ( $cit as $value )
    {
        // add to query
        $sql1 .= "('".$cit->key()."','" .$cit->current()."')";
       

        if( $cit->hasNext() )
        {
            $sql1 .= ",";
        }
    }

I want to pull some records  plus unique key from one mysql table and then use them as questions in a form/questionnaire.

The post form will have six radio buttons  to the right of the fields pulled with the key posted but hidden and will wirte to another table.

I guess this is a templating question- as I've successfully retrieved the table 1 records via a query - I just can't seem to use them in the form. Below is the code I'm using to view table "PCM1"

<?php // query.php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);

if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());

mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());

$query = "SELECT * FROM pcm1";
$result = mysql_query($query);

if (!$result) die ("Database access failed: " . mysql_error());

$rows = mysql_num_rows($result);

for ($j = 0 ; $j < $rows ; ++$j)
{
echo 'RID: '   . mysql_result($result,$j,'mcde')  ;
echo 'Statement: '    . mysql_result($result,$j,'oppclub')    . '<br />';

;
}

How do you do this? Thanks in advance.

Hello, me again.

I have created a little forum - very very basic.

main_forum.php :

Code: [Select]
<?php
$host="localhost"; // Host name 
$username="yvonnedp"; // Mysql username 
$password="yvonne"; // Mysql password 
$db_name="forum"; // Database name 
$tbl_name="forum_question"; // Table name 

// Connect to server and select databse.
mysql_connect('localhost', 'yvonnedp', 'yvonne')or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name ORDER BY id";
// OREDER BY id DESC is order result by descending 
$result=mysql_query($sql);
?>
<table width="90%" border="0" align="center" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td width="6%" align="center" bgcolor="#E6E6E6"><strong>#</strong></td>
<td width="53%" align="center" bgcolor="#E6E6E6"><strong>Topic</strong></td>
<td width="15%" align="center" bgcolor="#E6E6E6"><strong>Views</strong></td>
<td width="13%" align="center" bgcolor="#E6E6E6"><strong>Replies</strong></td>
<td width="13%" align="center" bgcolor="#E6E6E6"><strong>Date/Time</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result)){ // Start looping table row 
?>
<tr>
<td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
<td bgcolor="#FFFFFF"><a href="view_topic.php?id=<? echo $rows['id']; ?>"><? echo $rows['topic']; ?></a><BR></td>
<td align="center" bgcolor="#FFFFFF"><? echo $rows['view']; ?></td>
<td align="center" bgcolor="#FFFFFF"><? echo $rows['reply']; ?></td>
<td align="center" bgcolor="#FFFFFF"><? echo $rows['datetime']; ?></td>
</tr>

<?php
// Exit looping and close connection 
}
mysql_close();
?>
create_topic.php :

Code: [Select]
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form id="form1" name="form1" method="post" action="add_topic.php">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td colspan="3" bgcolor="#E6E6E6"><strong>Create New Topic</strong> </td>
</tr>
<tr>
<td width="14%"><strong>Topic</strong></td>
<td width="2%">:</td>
<td width="84%"><input name="topic" type="text" id="topic" size="50" /></td>
</tr>
<tr>
<td valign="top"><strong>Detail</strong></td>
<td valign="top">:</td>
<td><textarea name="detail" cols="50" rows="3" id="detail"></textarea></td>
</tr>
<tr>
<td><strong>Name</strong></td>
<td>:</td>
<td><input name="name" type="text" id="name" size="50" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td><input type="submit" name="Submit" value="Submit" /> <input type="reset" name="Submit2" value="Reset" /></td>
</tr>
</table>
</td>
</form>
</tr>
</table>
add_topic.php :

Code: [Select]
<?php
$host="localhost"; // Host name 
$username="yvonnedp"; // Mysql username 
$password="yvonne"; // Mysql password 
$db_name="forum"; // Database name 
$tbl_name="forum_question"; // Table name 

// Connect to server and select database.
mysql_connect('localhost', 'yvonnedp', 'yvonne')or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get data that sent from form 
$topic=$_POST['topic'];
$detail=$_POST['detail'];
$name=$_POST['name'];

$datetime=date("d/m/y h:i:s"); //create date time

$sql="INSERT INTO $tbl_name(topic, detail, name, datetime)VALUES('$topic', '$detail', '$name', '$datetime')";
$result=mysql_query($sql);

if($result){
echo "Successful<BR>";
echo "<a href=main_forum.php>View your topic</a>";
 
 $email_from = "forums@thenewme.co.za";
     $email_to = "info@thenewme.co.za";
     $email_subject = "The New Me - New Forum Topic";

     $email_message = "A new topic has been posted on the Forum!\n";
     $email_message .= "Check it out : http://www.thenewme.co.za/forum/main_forum.php";
     
     $headers = 'From: '.$email_from."\r\n";
 'Reply-To: '.$email_from."\r\n" . 'X-Mailer: PHP/' . phpversion();
  @mail($email_to, $email_subject, $email_message, $headers);


}
else {
echo "ERROR";
}
mysql_close();
?>
add_answer.php :

Code: [Select]
<?php
 
$host="localhost"; // Host name 
$username="yvonnedp"; // Mysql username 
$password="yvonne"; // Mysql password 
$db_name="forum"; // Database name 
$tbl_name="forum_answer"; // Table name 

// Connect to server and select databse.
mysql_connect('localhost', 'yvonnedp', 'yvonne')or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// Get value of id that sent from hidden field 
$id=$_POST['id'];

// Find highest answer number. 
$sql="SELECT MAX(a_id) AS Maxa_id FROM $tbl_name WHERE question_id='$id'";
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);

// add + 1 to highest answer number and keep it in variable name "$Max_id". if there no answer yet set it = 1 
if ($rows) {
$Max_id = $rows['Maxa_id']+1;
}
else {
$Max_id = 1;
}

// get values that sent from form 
$a_name=$_POST['a_name'];
$a_answer=$_POST['a_answer']; 

$datetime=date("d/m/y H:i:s"); // create date and time 

// Insert answer 
$sql2="INSERT INTO $tbl_name(question_id, a_id, a_name, a_answer, a_datetime)VALUES('$id', '$Max_id', '$a_name', '$a_answer', '$datetime')";
$result2=mysql_query($sql2);

if($result2){
echo "Successful<BR>";
echo "<a href='view_topic.php?id=".$id."'>View your answer</a>";

// If added new answer, add value +1 in reply column 
$tbl_name2="forum_question";
$sql3="UPDATE $tbl_name2 SET reply='$Max_id' WHERE id='$id'";
$result3=mysql_query($sql3);

 $email_from = "forums@thenewme.co.za";
     $email_to = "info@thenewme.co.za";
     $email_subject = "The New Me - New Forum Answer";

     $email_message = "A new answer to a forum topic has been posted on the Forum!\n";
     $email_message .= "Check it out : http://www.thenewme.co.za/forum/main_forum.php";
     
     $headers = 'From: '.$email_from."\r\n";
 'Reply-To: '.$email_from."\r\n" . 'X-Mailer: PHP/' . phpversion();
  @mail($email_to, $email_subject, $email_message, $headers);

}
else {
echo "ERROR";
}

mysql_close();
?>
view_topic :

Code: [Select]
<?php
$tbl_name2="forum_answer"; // Switch to table "forum_answer" 

$sql2="SELECT * FROM $tbl_name2 WHERE question_id='$id'";
$result2=mysql_query($sql2);

while($rows=mysql_fetch_array($result2)){
?>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td bgcolor="#F8F7F1"><strong>ID</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><? echo $rows['a_id']; ?></td>
</tr>
<tr>
<td width="18%" bgcolor="#F8F7F1"><strong>Name</strong></td>
<td width="5%" bgcolor="#F8F7F1">:</td>
<td width="77%" bgcolor="#F8F7F1"><? echo $rows['a_name']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Answer</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><? echo $rows['a_answer']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Date/Time</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><? echo $rows['a_datetime']; ?></td>
</tr>
</table></td>
</tr>
</table><br>

<?
}

$sql3="SELECT view FROM $tbl_name WHERE id='$id'";
$result3=mysql_query($sql3);

$rows=mysql_fetch_array($result3);
$view=$rows['view'];

// if have no counter value set counter = 1
if(empty($view)){
$view=1;
$sql4="INSERT INTO $tbl_name(view) VALUES('$view') WHERE id='$id'";
$result4=mysql_query($sql4);
}

// count more value
$addview=$view+1;
$sql5="update $tbl_name set view='$addview' WHERE id='$id'";
$result5=mysql_query($sql5);

mysql_close();
?>
the problem is. With add_answer, I am not able to insert an answer. This has worked for the very first question, but all subsequent questions, I am unable to add answers. It keeps telling me ERROR.

This is where the live forum is : http://www.thenewme.co.za/forum/main_forum.php

Can it be an issue with the field that gets autmatically incremented? i have echo'd the data which get entered in when someone types in an answer, and everything is perfect!

Can anyone help me?

Hi,

I need to compare records in a table based on the 'datetime' field, like if the difference between time is less than 15min.

need to combine the records(rows) as a single resultant row until the time difference between them  is less than 15min and when a record's time difference is >15min that should return as another row and so on. Please find following sample table and the required output format

Sample Table:

Code: [Select]
ID NAME DATETIME
1   aaa    2011-10-10 06:30:00
2   bbb    2011-10-10 06:33:00
3   ccc    2011-10-10 06:38:00
4   ddd    2011-10-10 06:40:00
5   eee    2011-10-10 07:10:00
6   ffff    2011-10-10 07:14:00
7   sss    2011-10-10 08:16:00
8   jjj    2011-10-10 08:26:00
9   kkk    2011-10-10 08:28:00
10  mm    2011-10-10 09:46:00
11  ppp    2011-10-10 09:49:00
12  qqq    2011-10-10 09:52:00


Output Needed :

Code: [Select]
IDs START DATETIME END DATETIME
1,2,3,4 2011-10-10 06:30:00 2011-10-10 06:40:00
5,6 2011-10-10 07:10:00                      2011-10-10 07:14:00
7,8,9 2011-10-10 08:16:00 2011-10-10 08:28:00
10,11,12 2011-10-10 09:46:00                      2011-10-10 09:52:00

You can see 1st row in the output table having IDs 1,2,3,4 coz the time difference between them is less than 15minutes (it doesn't means time difference between start and end; it is actually the  time difference between current record and previous one ; ie; ID-1 and ID-2 have difference less than 15min and ID-2 & ID-3 have difference less than 15min and ID-3 & ID-4 have difference less than 15min, so those 4 records combine together as a single row also shows their start time and end time. Then you can see diff between ID-4 and ID-5 is greater than 15 min so it should display as new row, and so on)

How can I acheive above Output with mysql query ??

Please help..