Start Free Trial

asked on

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

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";
}

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'";

ASKER

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

ASKER

How do i link to the database?

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());
}

ASKER

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

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";
}
?>

ASKER

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

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?

ASKER

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

ASKER

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.

Try and echo the $sql and see what gets output to the browser.

ASKER

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

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

ASKER

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

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 />";

ASKER

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

ASKER

To update my last post:

I don't get a black screen just the normal No Records Found screen....

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>";
}
?>

ASKER

By the way line 148 is the '{' just before the:

$query_data = mysql_fetch_object($resultID);

Anthony

ASKER

Have saved that - i get a black screen when it runs though:

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

Anthony

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>";
}
?>

ASKER

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: https://www.experts-exchange.com/questions/22502310/PHP-script-help.html

ASKER

The following debug.php works, but gets not results:

www.archiveservices.co.uk/debug.php

anthony

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>";
}
?>

ASKER

Still nothing:

Do you know what the error logs mean?

I'm really confused...

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial