Solved

PHP script help

Posted on 2007-04-10
27
225 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:d10u4v
  • 15
  • 12
27 Comments
 
LVL 4

Expert Comment

by:ftsao
ID: 18884740
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
 
LVL 4

Expert Comment

by:ftsao
ID: 18884746
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
 

Author Comment

by:d10u4v
ID: 18884787
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
 

Author Comment

by:d10u4v
ID: 18884807
How do i link to the database?
0
 
LVL 4

Expert Comment

by:ftsao
ID: 18884851
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
 

Author Comment

by:d10u4v
ID: 18885085
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
 
LVL 4

Expert Comment

by:ftsao
ID: 18885126
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
 

Author Comment

by:d10u4v
ID: 18885384
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
 
LVL 4

Expert Comment

by:ftsao
ID: 18885439
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
 

Author Comment

by:d10u4v
ID: 18886003
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
 

Author Comment

by:d10u4v
ID: 18886028
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
 
LVL 4

Expert Comment

by:ftsao
ID: 18886658
Try and echo the $sql and see what gets output to the browser.
0
 

Author Comment

by:d10u4v
ID: 18888118
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 4

Expert Comment

by:ftsao
ID: 18889031
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
 

Author Comment

by:d10u4v
ID: 18889692
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
 
LVL 4

Expert Comment

by:ftsao
ID: 18889742
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
 

Author Comment

by:d10u4v
ID: 18889846
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
 

Author Comment

by:d10u4v
ID: 18889880
To update my last post:

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

Expert Comment

by:ftsao
ID: 18889962
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
 

Author Comment

by:d10u4v
ID: 18889968
By the way line 148 is the '{' just before the:

$query_data = mysql_fetch_object($resultID);

Anthony
0
 

Author Comment

by:d10u4v
ID: 18890043
Have saved that - i get a black screen when it runs though:

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

Anthony
0
 
LVL 4

Expert Comment

by:ftsao
ID: 18890072
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
 

Author Comment

by:d10u4v
ID: 18890176
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
 

Author Comment

by:d10u4v
ID: 18890271
The following debug.php works, but gets not results:

www.archiveservices.co.uk/debug.php

anthony
0
 
LVL 4

Expert Comment

by:ftsao
ID: 18890339
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
 

Author Comment

by:d10u4v
ID: 18890934
Still nothing:

Do you know what the error logs mean?

I'm really confused...
0
 
LVL 4

Accepted Solution

by:
ftsao earned 500 total points
ID: 18891241
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn how to count occurrences of each item in an array.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now