MYSQL Select query for displaying data on a table is not working

Question

I want to display the data loaded from database after it is inserted using the function add_data() but it's not displaying the data on the table.

if(isset($_POST['btn_add_details'])) {
add_data();
include("db_PSIS.php");
$sql2="SELECT * FROM sample_barcode WHERE IDRec='".$row['IDRec']."'";
$result2=mysql_query($sql2);
if(!$result2) {
echo "<h1>Could not process query this time. Please try again later!</h1>";
}
else {
while($row2=mysql_fetch_array($result2)) { 

echo "<form name='form2' method='POST'>";
echo "<table class='output' border=2 align=center>";
echo "<tr class='thcolor'>";
echo "<th>Parent</th>";
echo "<th>LOT Traveller No.</th>";
echo "<th>Datecode</th>";
echo "</tr>";
echo "<tr>";
echo "<td>".$row2['LotTraveller']."</td>";
echo "<td>".$row2['LotTraveller']."</td>";
echo "<td>".$row2['Datecode']."</td>";
echo "</tr>";
echo "</table>";
echo "</form>";
}
echo "<br><h1>Data successfully loaded!</h1>";
}
mysql_close($link);
}
?>

Here's the function for adding data on the database:

function add_data() {

include("db_PSIS.php");
$sql="INSERT INTO sample_barcode (LotTraveller, ShipmentLotNumber) VALUES ('".$_POST['traveller']."', '".$_POST['datecode']."')";
$result=mysql_query($sql);
$row=mysql_fetch_array($result);
mysql_close($link);

}

Here's the code for database connection:

<?php
$username="****";
$password="****";
$database="****";
$hostname="****";
$link=@mysql_connect($hostname,$username,$password)
or die ("...cannot connect database, using $username for $hostname");
mysql_query("set names 'utf8'"); //指定数据库字符集
mysql_select_db($database,$link);

?>

Here's the form input:

<table width="500" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>

<tr>
<th align="right">Parent Traveller: </th>
<td><input type="text" name="traveller" size="20" value="<?php if(empty($_POST['traveller'])) {echo ''; } else { echo $row2['LotTraveller']; } ?>"/></td>
</tr>
<tr>
<th align="right">Date Code: </th>
<td><input type="text" name="datecode" size="20" value="<?php if(empty($_POST['traveller'])) {echo ''; } else { echo $row2['ShipmentLotNumber']; } ?>" /></td>
</tr>
<tr>
<th align="right">Traveller 1: </th>
<td><input type="text" name="traveller1" size="20" /></td>
</tr>
<tr>
<th align="right">Date Code: </th>
<td><input type="text" name="datecode1" size="20" /></td>
</tr>
<tr>
<th align="right">Traveller 2: </th>
<td><input type="text" name="traveller2" size="20" /></td>
</tr>
<tr>
<th align="right">Date Code: </th>
<td><input type="text" name="datecode2" size="20" /></td>
</tr>
<tr>
<th align="right"> </th>
<td><input type="hidden" name="id" size="20" value="<?php if(empty($_POST['traveller'])) {echo ''; } else {echo $row2['IDRec'];} ?>"/></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" name="btn_add_details" id="btn_add_details" onClick="return check_submit();" value="Add the Detail(s)" onfocus="setStyle(this.id)"
    style="color: #000000;
    padding: 2px 5px;
    border: 2px solid;
    border-color: #7bf #07c #07c #4AA02C;
    background-color: #09f;
    font-family: Georgia, ..., serif;
    font-size: 18px;
    display: block;
    height: 30px;
    width: 200px;" /> </td>
</tr>
</table>

The loop should only be running on the row - from the tr to /tr. — user2182349
– user2182349, Commented Nov 12, 2015 at 2:12
You print a form for every row. As @user2182349 said echo only <tr> content in the while loop. Also where is the code for add_data(); Finally you use the deprecated mysql_* functions and furthermore you are in danger of SQL Injection. — Kostas Mitsarakis
– Kostas Mitsarakis, Commented Nov 12, 2015 at 2:16
It only display the message "Data successfully loaded!". but the table not displayed. — Kimlyn Tormes
– Kimlyn Tormes, Commented Nov 12, 2015 at 2:17
@KostasMitsarakis yeah i know about the deprecated mysql, its just i need to use it. Please see above, I add the function for adding data. — Kimlyn Tormes
– Kimlyn Tormes, Commented Nov 12, 2015 at 2:20

Nimantha · Accepted Answer · 2021-10-29 16:37:35Z

1

Since you stated that IDRec is your PK and is set to auto-increment, AND you are pulling records based on a single number... you will never get more than one result in your resultset.

Change this code block...

$sql2="SELECT * FROM sample_barcode WHERE IDRec='".$row['IDRec']."'";
$result2=mysql_query($sql2);
if(!$result2) {
echo "<h1>Could not process query this time. Please try again later!</h1>";
}
else {
while($row2=mysql_fetch_array($result2)) { 

echo "<form name='form2' method='POST'>";
echo "<table class='output' border=2 align=center>";
echo "<tr class='thcolor'>";
echo "<th>Parent</th>";
echo "<th>LOT Traveller No.</th>";
echo "<th>Datecode</th>";
echo "</tr>";
echo "<tr>";
echo "<td>".$row2['LotTraveller']."</td>";
echo "<td>".$row2['LotTraveller']."</td>";
echo "<td>".$row2['Datecode']."</td>";
echo "</tr>";
echo "</table>";
echo "</form>";
}
echo "<br><h1>Data successfully loaded!</h1>";
}
mysql_close($link);
}

To this...

$sql2="SELECT * FROM sample_barcode WHERE IDRec=".$row['IDRec']."";
$result2=mysql_query($sql2);
$row2=mysql_fetch_assoc($result2); // added this line
if(!$result2) {
echo "<h1>Could not process query this time. Please try again later!</h1>";
}
else {

echo "<form name='form2' method='POST'>";
echo "<table class='output' border=2 align=center>";
echo "<tr class='thcolor'>";
echo "<th>Parent</th>";
echo "<th>LOT Traveller No.</th>";
echo "<th>Datecode</th>";
echo "</tr>";
echo "<tr>";
echo "<td>".$row2['LotTraveller']."</td>";
echo "<td>".$row2['LotTraveller']."</td>";
echo "<td>".$row2['Datecode']."</td>";
echo "</tr>";
echo "</table>";
echo "</form>";
echo "<br><h1>Data successfully loaded!</h1>";
}
mysql_close($link);
}

In the first line I removed the single quotes around your IDRec - this is an integer so single quotes are not needed.

Removed your while loop - you will only ever get one result so a loop is not needed.

Also, this doesn't appear to actually be a form so you can also remove your <form> </form> tags.

Finally, start learning pdo_mysql. my_sql has been deprecated. Anyone still using this code will wake up one day to find out that their website has magically stopped functioning.

EDIT

In your add_data function, change it to this...

function add_data() {

include("db_PSIS.php");
$sql="INSERT INTO sample_barcode (LotTraveller, ShipmentLotNumber) VALUES ('".$_POST['traveller']."', '".$_POST['datecode']."')";
$result=mysql_query($sql);
$sql="SELECT * FROM sample_barcode ORDER BY IDRec DESC"; // added this line
$result=mysql_query($sql); // added this line
$row=mysql_fetch_assoc($result); // changed this line from array to assoc
mysql_close($link);
}

This will get the result of the data you just entered. I added a query to get the data you need to display.

edited Oct 29, 2021 at 16:37

Nimantha

6,5276 gold badges32 silver badges78 bronze badges

answered Nov 12, 2015 at 3:23

Kuya

7,3204 gold badges21 silver badges31 bronze badges

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

5 Comments

Kimlyn Tormes Over a year ago

It shows the message Could not process query this time. Please try again later!. I think there something wrong with IDRec, but I don't what it is.

Kuya Over a year ago

In your add_data() function... change $row=mysql_fetch_array($result); to $row=mysql_fetch_assoc($result);. Let me know what happens.

Kimlyn Tormes Over a year ago

It displayed "Data successfully loaded" and the table without the data. Also I changed $row['IDRec'] into $_POST['id'] in the select query.

Kimlyn Tormes Over a year ago

It displayed "Data successfully loaded" and the table without the data. Also I changed $row['IDRec'] into $_POST['id'] in the select query coz I'm trying to put the value of IDRec in the textbox for id.

Kimlyn Tormes Over a year ago

Please see above. i added the form inputs.

Collectives™ on Stack Overflow

MYSQL Select query for displaying data on a table is not working

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related