Ajax and PHP - Error in Insertion

Question

Im using Ajax to get a value from the Drop down box and display the result .Example, Im having three types of mess in drop down box (veg,non veg and senior mess). Here is my code from Sql part:

<?php
$q=$_GET["q"];

$con = mysql_connect('localhost', 'root', '');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("hostel", $con);

$sql="SELECT registration.hosteladmissionno,registration.student_name,registration.semester,messexp.billmonth,messexp.billyear,messexp.wastagecharge,exp_amount.blockexp FROM registration,messexp,blockexp WHERE registration.mess_type = '".$q."' && status_flag=1";

$result = mysql_query($sql);

echo "<table border='1' width=80%>
<tr>
 <th width=5%> S.No</th>
<th width=10%>H.Admin No</th>
<th width=10%>Student Name</th>
<th width=10%>Semester</th>
<th width=10%>Billmonth</th>
<th width=10%>Bill Year</th>
<th width=10%>Wastage Charge</th>
<th width=10%>Block Amount</th>
<th width=5%>No of Days</th>
</tr>";
 $i=0;
while($row = mysql_fetch_array($result))
  {
  $i=$i+1;
  echo "<tr>";
  echo "<td  align=center>" .$i."</td>";
  echo "<td size=10 align=center>" . $row['hosteladmissionno'] . "</td>";
  echo "<td size=35  align=center>" . $row['student_name'] . "</td>";
   echo "<td size=10 align=center>" . $row['semester'] . "</td>";
  echo "<td size=35  align=center>" . $row['billmonth'] . "</td>";
   echo "<td size=10 align=center>" . $row['billyear'] . "</td>";
  echo "<td size=35  align=center>" . $row['wastagecharge'] . "</td>";
  echo "<td size=35  align=center>" . $row['exp_amount'] . "</td>";
  echo "<td  align=center>  <input type='text' name='days' size=2> </td> ";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

Ajax Part:

    <script type="text/javascript">
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  }
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","messbill1.php?q="+str,true);
xmlhttp.send();
}
</script>

From this I wanna insert The displayed details while we select the mess. how do i do the insert query. The field names are from different Tables.

Forms part:

    <h2 align='center'><b>Mess Bill</h1></b>
<form action="messbill.php"  onchange="showUser(this.value)"/>
<center>
<table>
<?php
$link = mysql_connect("localhost", "root", "");
mysql_select_db("hostel",$link);

$query = "SELECT mess_type FROM mess_type";
$result = mysql_query($query);

print "<tr><td><b>MESS TYPE:</b></td><td><SELECT name='users' onchange='showUser(this.value)'>";
while ($line = mysql_fetch_array($result))
{
foreach ($line as $value)
{
print "<OPTION value='$value'";
}
print ">$value</OPTION>";
}
mysql_close($link);
print "</SELECT></td></tr>";
?>

You might want to look into sanitizing your input. All kinds of ugly can potentially happen if you don't. — Demian Brecht
– Demian Brecht, Commented Mar 22, 2011 at 5:48
@Demian Brecht you were kind enough to give some explanation... I just shouted out to get in first :D — Jacob
– Jacob, Commented Mar 22, 2011 at 5:50
hey guys, Lets forget the insertion part. Im getting the error in Displaying details.:( — Coolbreeze
– Coolbreeze, Commented Mar 22, 2011 at 5:51
$sql="SELECT registration.hosteladmissionno,registration.student_name,registration.semester,messexp.billmonth,messexp.billyear,messexp.wastagecharge,exp_amount.blockexp FROM registration,messexp,blockexp WHERE registration.mess_type = '".$q."' && status_flag=1"; — Coolbreeze
– Coolbreeze, Commented Mar 22, 2011 at 5:52

Community · Accepted Answer · 2023-11-14 22:25:35Z

1

You aren't checking the return from mysql_query, which returns false on error.

You should check the return value, then check the error given:

if ($result === false) {
    //die is ok to use in development, 
    //use something you can handle the error with in production.
    die(mysql_error());
}

The error in your query is you are using && and not AND which is expected by MySQL.

Note: Assuming you have not hidden notices, you should see:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given

Its best to develop with all error_reporting on E_ALL, so you can see any notices/warning that may be causing you errors.

Here is your query in more readable formatting:

SELECT 
    registration.hosteladmissionno,
    registration.student_name,
    registration.semester,
    messexp.billmonth,
    messexp.billyear,
    messexp.wastagecharge,
    exp_amount.blockexp // Trying to select from a table not included in the FROM
FROM 
    registration,
    messexp,
    blockexp // I'm assuming you want exp_amount from this table.
WHERE 
    registration.mess_type = '".$q."' 
    AND status_flag=1 // Changed to be using AND not &&

Did you mean to select blockexp.exp_amount?

The way you current query is, its saying fetch the values in the exp_amount table in the column blockexp (which doesn't exist or hasn't been included anyway.)

If you want all the fields from blockexp, use blockexp.*

The next issue you are going to have is you aren't specifying how these tables are joined at all. So I believe you are going to get every possible combination of rows.

edited Nov 14, 2023 at 22:25

CommunityBot

11 silver badge

answered Mar 22, 2011 at 5:56

Jacob

8,3541 gold badge26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Coolbreeze Over a year ago

hello jacob, thanks for your suggestion. I tried your method. Im getting this Error --> Unknown column 'exp_amount.blockexp' in 'field list'

Coolbreeze Over a year ago

Yeah exactly.. I had that Warning.. Thats what made me sick! now its gone and i have pasted you the error which i got by using your method.

Jacob Over a year ago

@Dinzy you aren't selecting from a exp_amount table... So the field is unknown, just as the error says.

Jacob Over a year ago

@Dinzy have you tried generating the results you want straight into mysql or phpmyadmin or something first? before trying to write it in PHP?

Coolbreeze Over a year ago

@jacob: Im trying to generate the results straight from Mysql into Table coloums in Front End.The Php part which is involved in here is, Where i get the values from the List box, Other than that its Mysql.

|

Collectives™ on Stack Overflow

Ajax and PHP - Error in Insertion

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related