PHP script help

Hi,

I have two tables in my MySQL database :

tblTagDetails
TagNumber
CustomerCode
Weight
ShreddingDate
ShredConfirmationCode

tblShreddingDetails
ShredConfirmationCode
CertificateLink

A page on my website has a form with two fields on it:  TagNumber and CustomerCode

What i am trying to do is generate a php script which will return the following information:

When a TagNumber and CustomerCode match that informatin in the Database it shows the following fields:

The TagNumber
The CusomerCode
The Weight
The ShreddingDate
The ShredConfirmationCode
The CertificateLink

The user will be able to see the details fro the gieven TagNumber and beable to follow the link the the Certificate.

I hope someone can help me generate the required script.

Regards,
Anthony
d10u4vAsked:
Who is Participating?
 
ftsaoCommented:
The error below was my fault as I forgot to remove a few lines of code that ddin't apply to your example.

[client 74.15.170.253] PHP Fatal error:  Call to undefined function:  sql_error() in /home/vhosts/archiveservices.co.uk/var/www/html/onlineshred/debug.php on line 10, referer: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_22502310.html

The other error you posted (see below) is likely because the dbconnect.inc.php file was not included.

[client 74.15.170.253] PHP Warning:  mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/vhosts/archiveservices.co.uk/var/www/html/onlineshred/result.php on line 148, referer: http://www.archiveservices.co.uk/onlineshred/checkshred.html
0
 
ftsaoCommented:
I assume the ShredConfirmationCode in both tables are unique and can be used to link the two tables together.

Adjust the following assignments of the POSTed vars as needed if you are using GET and remove intval() if the tag number or customer code is a non-integer value.

$TagNumber = intval($_POST['TagNumber']);
$CustomerCode = intval($_POST['CustomerCode']);

$sql = "SELECT TagNumber, CustomerCode, Weight, ShreddingDate, ShredConfirmationCode, CertificateLink
        FROM tblTagDetails AS a, tblShreddingDetails AS b
        WHERE a.ShredConfirmationCode = b.ShredConfirmationCode
          AND TagNumber = '$TagNumber'
          AND CustomerCode = '$CustomerCode'";
$resultID = mysql_query($sql, $linkID);

// record(s) found
if (mysql_num_rows($resultID) > 0)
{
   $query_data = mysql_fetch_object($resultID);
   
   echo "Tag Number: $query_data->TagNumber<br />";
   echo "Customer Code: $query_data->CustomerCode<br />";
   echo "Weight: $query_data->Weight<br />";
   echo "Shredding Date: $query_data->ShreddingDate<br />";
   echo "Shred Confirmation Code: $query_data->ShredConfirmationCode<br />";
   echo "Certificate Link: $query_data->CertificateLink<br />";
}
// no record found
else
{
   echo "No record found";
}
0
 
ftsaoCommented:
Sorry, adjust the sql to the following to specify which table the ShredConfirmationCode is in to avoid the "ShredConfirmationCode is ambiguous" error:

$sql = "SELECT TagNumber, CustomerCode, Weight, ShreddingDate, a.ShredConfirmationCode, CertificateLink
        FROM tblTagDetails AS a, tblShreddingDetails AS b
        WHERE a.ShredConfirmationCode = b.ShredConfirmationCode
          AND TagNumber = '$TagNumber'
          AND CustomerCode = '$CustomerCode'";
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
d10u4vAuthor Commented:
Great!  Thanks for you help.

When this is linked to a submit button on the form, how do i show the results on another page once submitted?
Anthony
0
 
d10u4vAuthor Commented:
How do i link to the database?
0
 
ftsaoCommented:
Generally you want to use a single include file for you DB connection. Name the file something like dbconnect.inc.php and include it in the PHP pages that require database interaction (include_once 'dbconnect.inc.php';). Change the name of the database, username and password below to your needs.


$db_host = 'localhost';
$db_name = 'your_db_name';
$db_username = 'your_db_username';
$db_password = 'your_db_password';

// connect to database server
if (!$linkID = mysql_connect($db_host, $db_username, $db_password))
{
   die('SQL ERROR ' . mysql_errno() . ': ' . mysql_error());
}
// if database connection succeeded, select the database
else if (!mysql_select_db($db_name, $linkID))
{
   die('SQL ERROR ' . mysql_errno() . ': ' . mysql_error());
}
0
 
d10u4vAuthor Commented:
I have sorted the PHP file to access the database, but still don't know how to impliment the comple script.  Sorry to be a pain - i am very new to php and database manipulation :)

What exactly do i have to do to get the results from the database to the results page?

Regards,
Anthony
0
 
ftsaoCommented:
Save the following as form.html:

<html>
<head>
   <title>Enter Tag Number and Customer Code</title>
</head>

<body>

Enter Your Tag Number and Customer Code

<form action="result.php" method="post" name="myform">
Tag Number: <input type="text" name="TagNumber" />
<br />
Customer Code: <input type="text" name="CustomerCode'" />
<br />
<input type="submit" name="Submit" />
</form>

</body>
</html>

---------------------------------------------------------------------------
Save the following as dbconnect.inc.php:

<?php
$db_host = 'localhost';
$db_name = 'your_db_name';
$db_username = 'your_db_username';
$db_password = 'your_db_password';

// connect to database server
if (!$linkID = mysql_connect($db_host, $db_username, $db_password))
{
   die('SQL ERROR ' . mysql_errno() . ': ' . mysql_error());
}
// if database connection succeeded, select the database
else if (!mysql_select_db($db_name, $linkID))
{
   die('SQL ERROR ' . mysql_errno() . ': ' . mysql_error());
}
?>
---------------------------------------------------------------------------

Save the following as result.php:

<?php
include_once 'dbconnect.inc.php';

$TagNumber = intval($_POST['TagNumber']);
$CustomerCode = intval($_POST['CustomerCode']);
?>
<html>
<head>
   <title>Tag Number and Customer Details</title>
</head>

<body>
<h1>Tag Number and Customer Details</h1>

<?php
$sql = "SELECT TagNumber, CustomerCode, Weight, ShreddingDate, ShredConfirmationCode, CertificateLink
        FROM tblTagDetails AS a, tblShreddingDetails AS b
        WHERE a.ShredConfirmationCode = b.ShredConfirmationCode
          AND TagNumber = '$TagNumber'
          AND CustomerCode = '$CustomerCode'";
$resultID = mysql_query($sql, $linkID);

// record(s) found
if (mysql_num_rows($resultID) > 0)
{
   $query_data = mysql_fetch_object($resultID);
   
   echo "Tag Number: $query_data->TagNumber<br />";
   echo "Customer Code: $query_data->CustomerCode<br />";
   echo "Weight: $query_data->Weight<br />";
   echo "Shredding Date: $query_data->ShreddingDate<br />";
   echo "Shred Confirmation Code: $query_data->ShredConfirmationCode<br />";
   echo "Certificate Link: $query_data->CertificateLink<br />";
}
// no record found
else
{
   echo "No record found";
}
?>
0
 
d10u4vAuthor Commented:
Hi,

I have this so far:

http://www.archiveservices.co.uk/onlineshred/checkshred.html

there are two records in the MySQL database
customercode:26256
tagnumber 111111 & 222222

I have tried to enter them but get 'No Record Found'

Any ideas?

Anthony
0
 
ftsaoCommented:
Do you have access to phpMyAdmin (or even MySQL command line) to verify that the database records exist in both tables on your live server?

If so, check that the records are present in tblTagDetails and tblShreddingDetails and try to run the following SQL:

SELECT TagNumber, CustomerCode, Weight, ShreddingDate, ShredConfirmationCode, CertificateLink
        FROM tblTagDetails AS a, tblShreddingDetails AS b
        WHERE a.ShredConfirmationCode = b.ShredConfirmationCode
          AND TagNumber = '111111'
          AND CustomerCode = '26256'
What results do you get?
0
 
d10u4vAuthor Commented:
Hi,

I can browse the data stored in the database and can see the the data n the database.  My browser doesn't like me trying to run the SQL in PHPMyAdmin i get a page error...

Any ideas still... :)

Anthony

0
 
d10u4vAuthor Commented:
I get the following error when i run the SQL with PHPmyAdmin:

An appropriate representation of the requested resource /read_dump.php could not be found on this server.

0
 
ftsaoCommented:
Try and echo the $sql and see what gets output to the browser.
0
 
d10u4vAuthor Commented:
I get this:

SELECT TagNumber, CustomerCode, Weight, ShreddingDate, ShredConfirmationCode, CertificateLink FROM tblTagDetails AS a, tblShreddingDetails AS b WHERE a.ShredConfirmationCode = b.ShredConfirmationCode AND TagNumber = 111111 AND CustomerCode = 26256

So i'm getting the fields from the first page ok :)

but thats it....

Anthony
0
 
ftsaoCommented:
Sorry I made the same typo from my first post when I posted at 04.10.2007 at 01:14PM PDT. The SQL should be:

SELECT TagNumber, CustomerCode, Weight, ShreddingDate, a.ShredConfirmationCode, CertificateLink
FROM tblTagDetails AS a, tblShreddingDetails AS b
WHERE a.ShredConfirmationCode = b.ShredConfirmationCode
AND TagNumber = '$TagNumber'
AND CustomerCode = '$CustomerCode'";

"there are two records in the MySQL database
customercode:26256
tagnumber 111111 & 222222"

Is there a matching record in tblShreddingDetails for the two records in tblTagDetails?  The SQL I provided is trying to join the data using the ShredConfirmationCode field from BOTH tables. Your data should look something like:

tblTagDetails
---------------
TagNumber: 111111
CustomerCode: 26256
Weight: 500
ShreddingDate: 2007-01-01
ShredConfirmationCode: 12345

tblShreddingDetails
-----------------------
ShredConfirmationCode: 12345
CertificateLink: http://www.example.com
0
 
d10u4vAuthor Commented:
Still no joy.  If you follow the link i posted you will see i have included the SQL on the results page.  The cutomercode and the tagnumber are being poulated and used - but still nothing.

Yes both tables have a field ShredConfirmationCode with the same number 987654321.

Anthony
0
 
ftsaoCommented:
Strange, let's try a more simplified version of the SQL where we only query the tblTagDetails table. Hopefully you'll get something.

$sql = "SELECT TagNumber, CustomerCode, Weight, ShreddingDate, ShredConfirmationCode
        FROM tblTagDetails
        WHERE TagNumber = '$TagNumber'
          AND CustomerCode = '$CustomerCode'";
$resultID = mysql_query($sql, $linkID);


$query_data = mysql_fetch_object($resultID);
   
echo "Tag Number: $query_data->TagNumber<br />";
echo "Customer Code: $query_data->CustomerCode<br />";
echo "Weight: $query_data->Weight<br />";
echo "Shredding Date: $query_data->ShreddingDate<br />";
echo "Shred Confirmation Code: $query_data->ShredConfirmationCode<br />";
0
 
d10u4vAuthor Commented:
I get a black screen now with the address as:

http://www.archiveservices.co.uk/onlineshred/result.php

There is an error log on my domain which says the following if it helps:

[client 74.15.170.253] PHP Warning:  mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/vhosts/archiveservices.co.uk/var/www/html/onlineshred/result.php on line 148, referer: http://www.archiveservices.co.uk/onlineshred/checkshred.html

Anthony
0
 
d10u4vAuthor Commented:
To update my last post:

I don't get a black screen just the normal No Records Found screen....
0
 
ftsaoCommented:
I'm at a loss here. Can you save the following as debug.php. This will output all the records in both tables so I can get a better look at the data and see what may be missing.

<?php
include_once 'dbconnect.inc.php';

// turn error reporting on
error_reporting(E_ALL ^ E_NOTICE);

$sql = "SELECT TagNumber, CustomerCode, Weight, ShreddingDate, ShredConfirmationCode
        FROM tblTagDetails";
$resultID = mysql_query($sql, $linkID)
   or die(sql_error(__LINE__, __FILE__));

echo '<b>Output from tblTagDetails</b><br>';
   
while ($query_data = mysql_fetch_object($resultID))
{
   echo "TagNumber: $query_data->TagNumber<br>";
   echo "CustomerCode: $query_data->CustomerCode<br>";
   echo "Weight : $query_data->Weight <br>";
   echo "ShreddingDate: $query_data->ShreddingDate<br>";
   echo "ShredConfirmationCode : $query_data->ShredConfirmationCode<br><br>";
}

echo "<hr>";

$sql = "SELECT ShredConfirmationCode, CertificateLink
        FROM tblShreddingDetails";
$resultID = mysql_query($sql, $linkID)
   or die(sql_error(__LINE__, __FILE__));

echo '<b>Output from tblShreddingDetails</b><br>';
   
while ($query_data = mysql_fetch_object($resultID))
{
   echo "ShredConfirmationCode: $query_data->ShredConfirmationCode<br>";
   echo "CertificateLink: $query_data->CertificateLink<br>";
}
?>
0
 
d10u4vAuthor Commented:
By the way line 148 is the '{' just before the:

$query_data = mysql_fetch_object($resultID);

Anthony
0
 
d10u4vAuthor Commented:
Have saved that - i get a black screen when it runs though:

www.archiveservices.co.uk/onlineshred/debug.php

Anthony
0
 
ftsaoCommented:
Sorry, update that debug.php again with the following.

<?php
include_once 'dbconnect.inc.php';

// turn error reporting on
error_reporting(E_ALL ^ E_NOTICE);

$sql = "SELECT TagNumber, CustomerCode, Weight, ShreddingDate, ShredConfirmationCode
        FROM tblTagDetails";
$resultID = mysql_query($sql, $linkID);

echo '<b>Output from tblTagDetails</b><br>';
   
while ($query_data = mysql_fetch_object($resultID))
{
   echo "TagNumber: $query_data->TagNumber<br>";
   echo "CustomerCode: $query_data->CustomerCode<br>";
   echo "Weight : $query_data->Weight <br>";
   echo "ShreddingDate: $query_data->ShreddingDate<br>";
   echo "ShredConfirmationCode : $query_data->ShredConfirmationCode<br><br>";
}

echo "<hr>";

$sql = "SELECT ShredConfirmationCode, CertificateLink
        FROM tblShreddingDetails";
$resultID = mysql_query($sql, $linkID);

echo '<b>Output from tblShreddingDetails</b><br>';
   
while ($query_data = mysql_fetch_object($resultID))
{
   echo "ShredConfirmationCode: $query_data->ShredConfirmationCode<br>";
   echo "CertificateLink: $query_data->CertificateLink<br>";
}
?>
0
 
d10u4vAuthor Commented:
The following error has been logged from runnig the debug.php

[client 74.15.170.253] PHP Fatal error:  Call to undefined function:  sql_error() in /home/vhosts/archiveservices.co.uk/var/www/html/onlineshred/debug.php on line 10, referer: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_22502310.html
0
 
d10u4vAuthor Commented:
The following debug.php works, but gets not results:

www.archiveservices.co.uk/debug.php

anthony
0
 
ftsaoCommented:
Try this debug.php. This uses mysql_fetch_array instead of mysql_fetch_object. I'm afraid I'm running out of ideas here.

<?php
include_once 'dbconnect.inc.php';

// turn error reporting on
error_reporting(E_ALL ^ E_NOTICE);

$sql = "SELECT TagNumber, CustomerCode, Weight, ShreddingDate, ShredConfirmationCode
        FROM tblTagDetails";
$resultID = mysql_query($sql, $linkID);

echo '<b>Output from tblTagDetails</b><br>';
   
while ($query_data = mysql_fetch_array($resultID))
{
   echo "TagNumber: {$query_data['TagNumber']}<br>";
   echo "CustomerCode: {$query_data['CustomerCode']}<br>";
   echo "Weight : {$query_data['Weight']}<br>";
   echo "ShreddingDate: {$query_data['ShreddingDate']}<br>";
   echo "ShredConfirmationCode : {$query_data['ShredConfirmationCode']}<br><br>";
}

echo "<hr>";

$sql = "SELECT ShredConfirmationCode, CertificateLink
        FROM tblShreddingDetails";
$resultID = mysql_query($sql, $linkID);

echo '<b>Output from tblShreddingDetails</b><br>';
   
while ($query_data = mysql_fetch_array($resultID))
{
   echo "ShredConfirmationCode: {$query_data['ShredConfirmationCode']}<br>";
   echo "CertificateLink: {$query_data['CertificateLink']}<br>";
}
?>
0
 
d10u4vAuthor Commented:
Still nothing:

Do you know what the error logs mean?

I'm really confused...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.