Page 1 of 1

Unable to insert data in database

Posted: Sun Nov 06, 2011 11:57 pm
by divyajoshi
Hey friends,
I am trying to insert data into database(MYSQL) through a HTML table.
Even though database table gets created but data does'nt gets inserted.
Can any one help me out in this . I am using wamp server 2.2 .
Here's the code
Program 1: COde for classi.php

Code: Select all

<html>
<body>
	<form action="showclassi.php" method="post">	
		TIME TABLE OF CLASS :<input type="text" name="clas"><b>( specify stream_ followed by semester in numerics... for eg. it_7 )</b>
	<table border="1">		
	<tr>
		<td rowspan="2"> LEC.NO</td>
		<td colspan="2">***MONDAY***</td>	
		<td colspan="2"> ***TUESDAY***</td>				
		<td colspan="2"> ***WEDNESDAY***</td>
		<td colspan="2">***THURSDAY***</td>		
		<td colspan="2">***FRIDAY***</td>
	</tr>	
	
		<tr>
			<td>Subject</td>
			<td>faculty</td>

			<td>Subject</td>
			<td>faculty</td>
	
			<td>Subject</td>
			<td>faculty</td>
	
			<td>Subject</td>
			<td>faculty</td>
	
			<td>Subject</td>
			<td>faculty</td>
		</tr>
		
		<tr>
			<td>1.</td>
						<td><input type="text" name="l11"></td>
						<td><input type="text" name="f11"></td>				
						<td><input type="text" name="l12"></td>
						<td><input type="text" name="f12"></td>
						<td><input type="text" name="l13"></td>							
						<td><input type="text" name="f13"></td>
						<td><input type="text" name="l14"></td>								
						<td><input type="text" name="f14"></td>
						<td><input type="text" name="l15"></td>								
						<td><input type="text" name="f15"></td>
		</tr>
		<tr>
			<td>2.</td>
						<td><input type="text" name="l21"></td>
						<td><input type="text" name="f21"></td>				
						<td><input type="text" name="l22"></td>
						<td><input type="text" name="f22"></td>
						<td><input type="text" name="l23"></td>							
						<td><input type="text" name="f23"></td>
						<td><input type="text" name="l24"></td>								
						<td><input type="text" name="f24"></td>
						<td><input type="text" name="l25"></td>								
						<td><input type="text" name="f25"></td>
		</tr>
		<tr>
			<td>3.</td>
						<td><input type="text" name="l31"></td>
						<td><input type="text" name="f31"></td>				
						<td><input type="text" name="l32"></td>
						<td><input type="text" name="f32"></td>
						<td><input type="text" name="l33"></td>							
						<td><input type="text" name="f33"></td>
						<td><input type="text" name="l34"></td>								
						<td><input type="text" name="f34"></td>
						<td><input type="text" name="l35"></td>								
						<td><input type="text" name="f35"></td>
		</tr>
		<tr>
			<td>4.</td>
						<td><input type="text" name="l41"></td>
						<td><input type="text" name="f41"></td>				
						<td><input type="text" name="l42"></td>
						<td><input type="text" name="f42"></td>
						<td><input type="text" name="l43"></td>							
						<td><input type="text" name="f43"></td>
						<td><input type="text" name="l44"></td>								
						<td><input type="text" name="f44"></td>
						<td><input type="text" name="l45"></td>								
						<td><input type="text" name="f45"></td>
		</tr>
		<tr>
			<td>5.</td>
						<td><input type="text" name="l51"></td>
						<td><input type="text" name="f51"></td>				
						<td><input type="text" name="l52"></td>
						<td><input type="text" name="f52"></td>
						<td><input type="text" name="l53"></td>							
						<td><input type="text" name="f53"></td>
						<td><input type="text" name="l54"></td>								
						<td><input type="text" name="f54"></td>
						<td><input type="text" name="l55"></td>								
						<td><input type="text" name="f55"></td>
		</tr>
		<tr>
			<td>6.</td>
						<td><input type="text" name="l61"></td>
						<td><input type="text" name="f61"></td>				
						<td><input type="text" name="l62"></td>
						<td><input type="text" name="f62"></td>
						<td><input type="text" name="l63"></td>							
						<td><input type="text" name="f63"></td>
						<td><input type="text" name="l64"></td>								
						<td><input type="text" name="f64"></td>
						<td><input type="text" name="l65"></td>								
						<td><input type="text" name="f65"></td>
		</tr>			
		<tr>
			<td>7.</td>
						<td><input type="text" name="l71"></td>
						<td><input type="text" name="f71"></td>				
						<td><input type="text" name="l72"></td>
						<td><input type="text" name="f72"></td>
						<td><input type="text" name="l73"></td>							
						<td><input type="text" name="f73"></td>
						<td><input type="text" name="l74"></td>								
						<td><input type="text" name="f74"></td>
						<td><input type="text" name="l75"></td>								
						<td><input type="text" name="f75"></td>
		</tr>
		<tr>
			<td>8.</td>
						<td><input type="text" name="l81"></td>
						<td><input type="text" name="f81"></td>				
						<td><input type="text" name="l82"></td>
						<td><input type="text" name="f82"></td>
						<td><input type="text" name="l83"></td>							
						<td><input type="text" name="f83"></td>
						<td><input type="text" name="l84"></td>								
						<td><input type="text" name="f84"></td>
						<td><input type="text" name="l85"></td>								
						<td><input type="text" name="f85"></td>			
		</tr>			
		</table>
				<input type="submit" value="Submit ">
	</form>
</body>
</html>


2.Progaram 2

Code: Select all

<?php

$var = $_POST["clas"];
$len = strlen($var);
echo "length of string passed is ".$len;

if(isset($_POST["clas"]) &&  strtolower($_POST["clas"]) == "it_7" )
    {
        $conn=mysql_connect("localhost","root");
        if(is_resource($conn))
        {
            echo "Connection done ";
        }
        $sql="create database ttclg";
        mysql_query($sql,$conn);
        if (!$conn)
        {
            die('Could not connect: ' . mysql_error());
        }
        else 
        {
            echo "connect succesfully to database";
        }
        mysql_select_db("ttclg",$conn);
        
        $sql2="create table it_7(
        lec varchar(15),
        f1 varchar(15),
        lec2 varchar(15),
        f2 varchar(15),
        lec3 varchar(15),
        f3 varchar(15),
        lec4 varchar(15),
        f4 varchar(15),
        lec5 varchar(15),
        f5 varchar(15),
        lec6 varchar(15),
        f6 varchar(15),
        lec7 varchar(15),
        f7 varchar(15),
        lec8 varchar(15),
        f8 varchar(15))";
        mysql_query($sql2,$conn);    
        
$sql3="insert into it_7(lec,f1,lec2,f2,lec3,f3,lec4,f4,lec5,f5,lec6,f6,lec7,f7,lec8,f8)values('$_POST[l11]', '$_POST[f11]', '$_POST[l12]', '$_POST[f12]', '$_POST[l13]', '$_POST[f13]', '$_POST[l14]', '$_POST[f14]', '$_POST[l15]', '$_POST[f15]')";
        
        mysql_query($sql3,$conn);        
     
        $sql4="insert into it_7(lec,f1,lec2,f2,lec3,f3,lec4,f4,lec5,f5,lec6,f6,lec7,f7,lec8,f8)values('$_POST[l21]', '$_POST[f21]', '$_POST[l22]', '$_POST[f22]', '$_POST[l23]', '$_POST[f23]', '$_POST[l24]', '$_POST[f24]', '$_POST[l25]', '$_POST[f25]')";
        
        mysql_query($sql4,$conn);
        
        $sql5="insert into it_7(lec,f1,lec2,f2,lec3,f3,lec4,f4,lec5,f5,lec6,f6,lec7,f7,lec8,f8)values('$_POST[l31]', '$_POST[f31]', '$_POST[l32]', '$_POST[f32]', '$_POST[l33]', '$_POST[f33]', '$_POST[l34]', '$_POST[f34]', '$_POST[l35]', '$_POST[f35]')";            
        
        mysql_query($sql5,$conn);
        $sql6="insert into it_7(lec,f1,lec2,f2,lec3,f3,lec4,f4,lec5,f5,lec6,f6,lec7,f7,lec8,f8)values('$_POST[l41]', '$_POST[f41]', '$_POST[l42]', '$_POST[f42]', '$_POST[l43]', '$_POST[f43]', '$_POST[l44]', '$_POST[f44]', '$_POST[l45]', '$_POST[f45]')";
        
        mysql_query($sql6,$conn);
        $sql7="insert into it_7(lec,f1,lec2,f2,lec3,f3,lec4,f4,lec5,f5,lec6,f6,lec7,f7,lec8,f8)values('$_POST[l51]', '$_POST[f51]', '$_POST[l52]', '$_POST[f52]', '$_POST[l53]', '$_POST[f53]', '$_POST[l54]', '$_POST[f54]', '$_POST[l55]', '$_POST[f55]')";
        
        mysql_query($sql7,$conn);
        $sql8="insert into it_7(lec,f1,lec2,f2,lec3,f3,lec4,f4,lec5,f5,lec6,f6,lec7,f7,lec8,f8)values('$_POST[l61]', '$_POST[f61]', '$_POST[l62]', '$_POST[f62]', '$_POST[l63]', '$_POST[f63]', '$_POST[l64]', '$_POST[f64]', '$_POST[l65]', '$_POST[f65]')";                
        mysql_query($sql8,$conn);
        
        $sql9="insert into it_7(lec,f1,lec2,f2,lec3,f3,lec4,f4,lec5,f5,lec6,f6,lec7,f7,lec8,f8)values('$_POST[l71]', '$_POST[f72]', '$_POST[l72]', '$_POST[f72]', '$_POST[l73]', '$_POST[f73]', '$_POST[l74]', '$_POST[f74]', '$_POST[l75]', '$_POST[f75]')";                
        mysql_query($sql9,$conn);
        
        $sql10="insert into it_7(lec,f1,lec2,f2,lec3,f3,lec4,f4,lec5,f5,lec6,f6,lec7,f7,lec8,f8)values('$_POST[l81]', '$_POST[f81]', '$_POST[l82]', '$_POST[f82]', '$_POST[l83]', '$_POST[f83]', '$_POST[l84]', '$_POST[f84]', '$_POST[l85]', '$_POST[f85]')";            
        mysql_query($sql10,$conn);
        
mysql_close($conn);
}

?>

Thanks in advance.

Re: Unable to insert data in database

Posted: Mon Nov 07, 2011 12:08 am
by social_experiment
You don't say what type of error (if any) you receive. Tip: Use statements similar to the one below in your production area to test for any errors resulting from mysql queries.

Code: Select all

<?php
mysql_query($sql3,$conn) or die(mysql_error()); 
?>

Re: Unable to insert data in database

Posted: Mon Nov 07, 2011 1:36 am
by twinedev
From quick glance, the issue is that you are naming 16 fields in your INSERT statement, yet are only feeding it 10 values (you are doing 1-8 for the rows, but giving it 1-5 for days of week).

Also, just to clean up code, here are some cleaned up copies of your code:

HTML:

Code: Select all

<html>
<body>
    <form action="showclassi.php" method="post">
        TIME TABLE OF CLASS :<input type="text" name="clas"><b>( specify stream_ followed by semester in numerics... for eg. it_7 )</b>
        <table border="1">
            <tr>
                <td rowspan="2"> LEC.NO</td>
                <td colspan="2">***MONDAY***</td>
                <td colspan="2">***TUESDAY***</td>
                <td colspan="2">***WEDNESDAY***</td>
                <td colspan="2">***THURSDAY***</td>
                <td colspan="2">***FRIDAY***</td>
            </tr>
            <tr>
                <?php for($d=1;$d<=5;$d++): ?>
                    <td>Subject</td>
                    <td>Faculty</td>
                <?php endfor; ?>
            </tr>
            <?php for($t=1;$t<=8;$t++): ?>
                <tr>
                    <td><?php echo $t; ?>.</td>
                    <?php for($d=1;$d<=5;$d++): ?>
                        <td><input type="text" name="l<?php echo $t,$d; ?>"></td>
                        <td><input type="text" name="f<?php echo $t,$d; ?>"></td>
                    <?php endfor; ?>
                </tr>
            <?php endfor; ?>
        </table>
        <input type="submit" value="Submit ">
    </form>
</body>
</html>
PHP:

Code: Select all

<?php

    if (count($_POST)>0 && isset($_POST["clas"]) &&  strtolower($_POST["clas"]) == "it_7") {

        $conn = mysql_connect('localhost','root','password')
                            or die ('Unable to access database server');

        echo "Connected to Database Server<br>\n";

        // Create database...
        $SQL = 'CREATE DATABASE `ttclg`';
        mysql_query($SQL)
            or die ('Unable to execute query: '.mysql_error());

        echo "Created Database<br>\n";

        mysql_select_db('ttclg',$conn)
            or die ('Unable to use database `ttclg`');

        // Create table .. ASSUMING colums 1-5 for each day of week based upon your values inserted...
        $SQL  = 'CREATE TABLE `it_7` (';
        for($t=1;$t<=5;$t++) {
            $SQL .= '`lec'.$t.'` VARCHAR(15), `f'.$t.'` VARCHAR(15),';
        }
        // Get rid of trailing comma from the loop above...
        $SQL = substr($SQL,0,-1);

        mysql_query($SQL)
            or die ('Unable to execute query: '.mysql_error());

        for($t=1;$t<=8;$t++) {
            // $SQL1 will always be the same, so only need to define it on first pass
            if($t==1) { $SQL1 = 'INSERT INTO `it_7` ('; }
            $SQL2 = '';
            for($d=1;$d<=5;$d++) {
                // $SQL1 will always be the same, so only need to define it on first pass
                if($t==1) { $SQL1 .= '`lec'.$d.'`,`f'.$d.'`,'; }
                $SQL2 .= '"'.mysql_real_escape_string($_POST['l'.$t.$d]).'","'.mysql_real_escape_string($_POST['f'.$t.$d]).'",';
            }
            // $SQL1 will always be the same, so only need to define it on first pass
            if($t==1) { $SQL1 = substr($SQL1,0,-1).') VALUES ('; }

            // Check to make sure the row wasn't completely empty before inserting
            if ($SQL2 != '"","","","","","","","","","",') {
                $SQL = $SQL1 . substr($SQL2,0,-1) . ')';

                mysql_query($SQL)
                    or die ('Unable to execute query: '.mysql_error());

                echo "Wrote Row $t of 8<br>\n";
            }
            else {
                echo "Row $t of 8 was complete blank, not inserted<br>\n";
            }
        }

        mysql_close($conn);

        echo "Sucessfully Wrote Data!<br><hr>\n";
    }

?>