php mySQL returns an empty query

Question

I'm trying to create a billing history page where users can see a table with their purchase history in table format. I'm trying to add a link to each row so that users can see the full invoice details for each order.

When they click on the link (I've included the script for the overview page after this script) this script is supposed to execute the query but I'm just getting an empty message query.

Can anyone spot the error?

Thanks!!

Eugenie

<?php

include("mainfile.php");
include(XOOPS_ROOT_PATH."/header.php");


$host="localhost"; // Host name 
$username="user"; // Mysql username 
$password="pass"; // Mysql password 
$db_name="db"; // Database name 
$tbl_name="table"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$TicketID=$_GET['TicketID'];

// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE TicketID='$TicketID'";
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);


// Get a specific result from the "example" table
$result = mysql_query($Sql) or die(mysql_error());


echo "<table width='100' border='1' cellpadding='0' cellspacing='0' id='records'>";
print "<h3 align='center'><strong>Billing History</strong></h3><p>";
echo "<tr>  
<th width='110' align='center'>Billing Date</th>
<th width='80' align='center'>Ticket #</th>  
<th align='center'>Project Title </th>
<th width='80' align='center'>Total GBP</th>



</tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr height='22' width='100' bordercolor='343434' align='center'><td>"; 
echo $row['date'];
echo "</td><td> "; 
echo $row['TicketID'];

echo "</td><td> "; 
echo $row['project'];
echo "</td><td> "; 
echo $row['grandTotal'];


} 

echo "</table>";

include(XOOPS_ROOT_PATH."/footer.php");

?>

This is the script that displays the full billing history and that contains the link with the script that executes the query (above).

<?php

include("mainfile.php");
include(XOOPS_ROOT_PATH."/header.php");


$host="localhost"; // Host name 
$username="username"; // Mysql username 
$password="password"; // Mysql password 
$db_name="db name"; // Database name 
$tbl_name="tbl name"; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name  WHERE uid='";
$sql=$sql .  $xoopsUser->uid("s") . "'  AND Paid='Y'";


$result=mysql_query($sql);
?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>

<tr>
<td align="center"><strong>Billing Date</strong></td>
<td align="center"><strong>Invoice Number</strong></td>
<td align="center"><strong>Description</strong></td>
<td align="center"><strong>Total GBP</strong></td>
<td align="center"><strong>View</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td><? echo $rows['date']; ?></td>
<td><? echo $rows['TicketID']; ?></td>
<td><? echo $rows['project']; ?></td>
<td><? echo $rows['grandTotal']; ?></td>


<td align="center"><a href="http://website.co.uk/site/viewInvoice.php?TicketID=<? echo     $rows['TicketID']; ?>">View</a></td>
</tr>

<?php
}
?>

</table>
</td>
</tr>
</table>

<?php
mysql_close();
include(XOOPS_ROOT_PATH."/footer.php");
?>

Use mysqli (or PDO) instead of mysql (will be deprecated soon) and have a look at SQL injection (stackoverflow.com/questions/60174/…). You are using the $_GET['TicketID'] without any checking/escaping. — Pieter
– Pieter, Commented Jul 24, 2013 at 15:49
You've got a number of issues. You're using mysql_*, which is in the process of being deprecated; you're also passing $_GET['TicketID'] directly into your SQL query, with no validation. That's incredibly insecure. Finally $result = mysql_query($Sql) will fail, because you've adding the query as $sql, and variable names are case-sensitive. — andrewsi
– andrewsi, Commented Jul 24, 2013 at 15:51
I did it again. I just made same mistake, which was fixed in another post and manage to do the same here, even though I looked for the mistake. Must be capital S blind! THANKS FOR YOUR HELP AGAIN! And thanks for the security warnings. Unfortunately I've got no idea how to fix the problem. Building a site myself. I hope it will start making money before all these changes take place and before I get into trouble with the GET command. Got no idea how to go about it. — Eugenie
– Eugenie, Commented Jul 24, 2013 at 16:30

Maximus2012 · Accepted Answer · 2013-07-24 15:55:21Z

2

change this:

// Get a specific result from the "example" table
$result = mysql_query($Sql) or die(mysql_error());

to this:

$result = mysql_query($sql) or die(mysql_error());

in viewInvoice.php but also look at the comments from other regarding security etc.

answered Jul 24, 2013 at 15:55

Maximus2012

1,8392 gold badges12 silver badges15 bronze badges

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

Comments

mgrueter · Accepted Answer · 2013-07-24 15:50:01Z

0

check these lines:

// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE TicketID='$TicketID'";
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);

// Get a specific result from the "example" table
$result = mysql_query($Sql) or die(mysql_error());

You are overwriting your $result variable with the return value from mysql_query which gets a parameter that doesn't exist: $Sql

PHP variable names are case-sensitive!

answered Jul 24, 2013 at 15:50

mgrueter

1,4207 silver badges15 bronze badges

4 Comments

mgrueter Over a year ago

Oh and: no need to do the whole mysql_query and mysql_fetch_array stuff twice

andrewsi Over a year ago

Especially when the first call doesn't have any check to see if it worked, too.

mgrueter Over a year ago

Yeah well, there's lots of stuff wrong in this snippet. I almost died reading those lines, mostly because it reminds me of the crap I wrote back when I started with PHP. So many bad memories ^^

andrewsi Over a year ago

I try not to think about the code I wrote back in the day. It will just scare me.

user3183111 · Accepted Answer · 2014-01-10 20:11:08Z

0

Change this

<a href="http://website.co.uk/site/viewInvoice.php?TicketID=<? echo $rows['TicketID']; ?>">View</a></td>

to this

<?php 
echo "href=http://website.co.uk/site/viewInvoice.php?TicketID=" . $rows['TicketID'] . ">View</a>";
?>

answered Jan 10, 2014 at 20:11

user3183111

3412 silver badges10 bronze badges

Comments

de_nuit · Accepted Answer · 2013-07-24 15:58:33Z

<?php

include("mainfile.php");
include(XOOPS_ROOT_PATH."/header.php");


$host="localhost"; // Host name 
$username="user"; // Mysql username 
$password="pass"; // Mysql password 
$db_name="db"; // Database name 
$tbl_name="table"; // Table name

mysql_connect($host, $username, $password)or die("cannot connect"); 
mysql_select_db($db_name)or die("cannot select DB");

// get value of id that sent from address bar
$TicketID=$_GET['TicketID'];

// Retrieve data from database 
$sql="SELECT * FROM ' ".$tbl_name."'  WHERE `TicketID`='".$TicketID."'";
$result  = mysql_query($sql);



echo "<table width='100' border='1' cellpadding='0' cellspacing='0' id='records'>";
print "<h3 align='center'><strong>Billing History</strong></h3><p>";
echo "<tr>  
<th width='110' align='center'>Billing Date</th>
<th width='80' align='center'>Ticket #</th>  
<th align='center'>Project Title </th>
<th width='80' align='center'>Total GBP</th></tr>";
// keeps getting the next row until there are no more to get
while ($row = mysql_fetch_array($result)) {
    // Print out the contents of each row into a table
    echo "<tr height='22' width='100' bordercolor='343434' align='center'><td>"; 
    echo $row['date'];
    echo "</td><td> "; 
    echo $row['TicketID'];

    echo "</td><td> "; 
    echo $row['project'];
    echo "</td><td> "; 
    echo $row['grandTotal'];
} 

echo "</table>";

include(XOOPS_ROOT_PATH."/footer.php");

?>

And the second script:

<?php

include("mainfile.php");
include(XOOPS_ROOT_PATH."/header.php");


$host="localhost"; // Host name 
$username="username"; // Mysql username 
$password="password"; // Mysql password 
$db_name="db name"; // Database name 
$tbl_name="tbl name"; // Table name 

// Connect to server and select database.
mysql_connect($host, $username, $password)or die("cannot connect"); 
mysql_select_db($db_name)or die("cannot select DB");

$sql="SELECT * FROM `".$tbl_name."`  WHERE `uid`='".$xoopsUser->uid("s"). "'  AND `Paid`='Y'";
$result=mysql_query($sql);
?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>

<tr>
<td align="center"><strong>Billing Date</strong></td>
<td align="center"><strong>Invoice Number</strong></td>
<td align="center"><strong>Description</strong></td>
<td align="center"><strong>Total GBP</strong></td>
<td align="center"><strong>View</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td><? echo $rows['date']; ?></td>
<td><? echo $rows['TicketID']; ?></td>
<td><? echo $rows['project']; ?></td>
<td><? echo $rows['grandTotal']; ?></td>


<td align="center"><a href="http://website.co.uk/site/viewInvoice.php?TicketID=<? echo $rows['TicketID']; ?>">View</a></td>
</tr>

<?php
}
?>

</table>
</td>
</tr>
</table>

<?php
mysql_close();
include(XOOPS_ROOT_PATH."/footer.php");
?>

sry. stupid me. try that..

Collectives™ on Stack Overflow

php mySQL returns an empty query

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related