php and mysql record selection trouble

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
cbcampbell
Forum Newbie
Posts: 19
Joined: Mon Jun 09, 2003 12:12 am

php and mysql record selection trouble

Post by cbcampbell »

I am having some trouble here with my code it seems. Here is what I am wanting to achieve. I have setup a table that creates invoices through a PHP webpage (to make it easy on the end user). Now, I also have a view link that allows the end user to see all the invoices that have been created - with only a few fields showing, plus a view link that should take the current record that the current view link is attached to and extract all columns from one record result and output it to a new page.
Here is the "queried" table code (the short table view of all records)...

/* go here (http://www.texascarts.com/invoices/view_all.php) to see the follow code in action */

Code: Select all

<?php
include('dbinfo.inc.php');

mysql_connect(localhost,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT * FROM invoices ORDER BY inv_date, inv_id ASC";
$result = mysql_query($query);
$num = mysql_numrows($result);

mysql_close();

echo "<b><center>View All Invoices</center></b><br><br>";

?>

<table border="0" cellspacing="8" cellpadding="8">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Date</font></th>
<th><font face="Arial, Helvetica, sans-serif">Invoice #</font></th>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>

</tr>

<?
$i=0;
while ($i < $num) {

$inv_date = mysql_result($result,$i,"inv_date");
$inv_id = mysql_result($result,$i,"inv_id");
$inv_cstname = mysql_result($result,$i,"inv_cstname");

?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$inv_date"; ?></font></td>
<td align="center"><font face="Arial, Helvetica, sans-serif"><? echo "$inv_id"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$inv_cstname"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="cur_invoice.php">View</a></font></td>
</tr>

<?
++$i;
}


echo "</table>";
?>
And here is the code for "cur_invoice.php" - the href of the View link...

Code: Select all

<?php
include('dbinfo.inc.php');

mysql_connect(localhost,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT * FROM invoices";

$result = mysql_query($query);

mysql_num_rows($result);

mysql_close();

$inv_id = mysql_result($result,"inv_id");
$inv_date = mysql_result($result,"inv_date");
$inv_cstname = mysql_result($result,"inv_cstname");

echo "$inv_id<br>$inv_date<br>$inv_cstname";
?>
I know my problem is with that view link that I have attached to each record.
If you'll notice when looking at the "view_all.php" page, I created a query that only shows 3 columns of each record (and these are fake records btw, I'm just using these for practice). Now, what I want this to do, is when I click on the view link associated with each record, it should open up a new page with all the columns of that one record - basically displaying the enire invoice.
I know my problem lies with the view link, I just don't know what to do.
jmarcv
Forum Contributor
Posts: 131
Joined: Tue Jul 29, 2003 7:17 pm
Location: Colorado

Post by jmarcv »

You need to pass the id to the cur_invoice.php page
<a href="cur_invoice.php?id=<? echo $inv_id;?> ">
, and the query:
$query = "SELECT * FROM invoices where id=$id";
dee
Forum Newbie
Posts: 2
Joined: Tue Aug 05, 2003 5:48 am

Post by dee »

i'm doing a similar thing but with a different challenge. i have a table of records with hyperlinks to check details of each record:

<td><a href=\"details1.php?ApplicationNo=" . $row['ApplicantDetails.ApplicationNo'] . "\">Details</a></td>

in the details1.php script, I extract the data from the database:

$query1 = "SELECT * FROM ApplicantDetails WHERE ApplicationNo='$ApplicationNo'";
$result1 = mysql_query($query1)
or die ("Couldn't execute query 1.");
$row1 = mysql_fetch_array($result1);
@extract($row1);

and display them in forms, for example:

<tr>
<td align='right'> Title </td>
<td> <input type='text' maxlength='40' size='40' name='App_Title' value='$App_Title'/>
</td>
</tr>

Problem: it works fine for the first case but when i go back to the first page to check details for ANOTHER application number, it still displays details for the first application number viewed.

HELP!!
jmarcv
Forum Contributor
Posts: 131
Joined: Tue Jul 29, 2003 7:17 pm
Location: Colorado

Post by jmarcv »

dee,

Its got to be in the first script then, so how about you post code of where the problem is, rather than the code that works?

Did you 'view Source' to see if all your links have the same ApplicationNo for some reason? Maybe a problem with your linkloop?
dee
Forum Newbie
Posts: 2
Joined: Tue Aug 05, 2003 5:48 am

Post by dee »

I did the 'view source' and ApplicationNo was correctly transferred for each record. The issue is that of reliability - at times correct details come up but at other times they do not. I guess it has to do with the 'extract' statement. PHP manual (http://www.php.net/manual/en/function.extract.php) indicates that the default is EXTR_OVERWRITE where 'If there is a collision, [it] overwrite[s] the existing variable'. Apparently the overwrite function fails at those times(?). Is there a way to get around this?

I tried explicitly using the overwrite function:

@extract($row1, EXTR_OVERWRITE)

it doen't work either!
jmarcv
Forum Contributor
Posts: 131
Joined: Tue Jul 29, 2003 7:17 pm
Location: Colorado

Post by jmarcv »

#1, get rid of the @ so you can see if there is an error.

#2, What collision? There should be NO variables defined yet anyway, right?

#3, did you output the sql statement to verify you have the right value?
print $query1;
#4, did you try it without extract? ie: use

Code: Select all

<input type='text' maxlength='40' size='40' name='App_Title' value='".row1&#1111;'App_Title']."'/>
instead of depending on extract?
Post Reply