Quick Script Help[

PHP
1

Hi,
I’m just writing a quick PHP script to grab some data from a mysql table and create pdf files out of it and I ahve this query:

SELECT * FROM coasters, parks WHERE coasters.id = 3 AND coasters.park_id = parks.id

It works fine when I run it in Navicat Mysql

But when I run it in my script and use the following line:

$coastername = $row['coasters.name'];

I get an undefined index error?

In Navicat MySQL, the table headings as one field has the same name in both the parks and coasters tables the headings in the query results show up as coasters.name and parks.name so why do I get an undefined index error:

The full snippet of code is:

$sql = 'SELECT * FROM coasters, parks WHERE coasters.id = 3 AND coasters.park_id = parks.id';

$result = mysql_query($sql);

$row = mysql_fetch_assoc($result);

$coastername = $row['coasters.name'];

$pdf=new FPDF();
$pdf->AddPage();
$pdf->SetMargins(0, 0, 0);
$pdf->SetXY(10,15);
$pdf->SetFont('Arial','B',30);
$pdf->Cell('', '', $row['name']);
$pdf->SetXY(10,35);
$pdf->Cell('', '', $row['parks.name']);
$pdf->Output($row['coasters.name'].'.pdf','F');
?>
2

$row should be an array. A handy way to see the contents of an array is to use print_r() or var_dump().

3

It doesn’t return the fields as $row[‘coaster.name’] but as $row[‘name’]. So you will need to do what crmalibu said and use print_r() or var_dump() to see the correct variable names.

4

Right, as two fields have the same name, the name field in both the coasters and parks tables. When I var_dump() $row It looks like the name in the parks table has overwritten the name in the coasters table. Is there a way to get around this same field name in two tables when accessing them both in the same query?

5

Use an ALIAS in your query. :wink:

6

Thanks for that, it did work. 2 issues:

1.) As I have a quite a few fields in my tables, It’s easier to use “*” to select all fields from the tables but then I get the conflicts and cannot use aliases to certain fields. Is there a way to select * but to set an alias on certain fields?

2.) My script is now not outputting anything. Just a white screen and no pdf file is being saved to the folder?

Can you spot any stupid mistakes anywhere?

<?php
require('../fpdf/fpdf.php');

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$db = 'rctneil_coasters';


$dbconnect = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error('Failed to connect to MySQL'));
$dbselect = mysql_select_db($db) or die(mysql_error('Failed to select the database'));

$sql = 'SELECT coasters.name as cname, parks.name as pname FROM coasters, parks WHERE coasters.id = 3 AND coasters.park_id = parks.id';

$result = mysql_query($sql);

$row = mysql_fetch_assoc($result);

while ($row = mysql_fetch_assoc($result)) {

$coastername = $row['cname'];
$parkname = $row['pname'];

$pdf=new FPDF();
$pdf->AddPage();
$pdf->SetMargins(0, 0, 0);
$pdf->SetXY(10,15);
$pdf->SetFont('Arial','B',30);
$pdf->Cell('', '', $coastername);
$pdf->SetXY(10,35);
$pdf->Cell('', '', $parkname);
$pdf->Output($coastername.' - '.$parkname.'.pdf','F');
}
?>
7

Sorry, 2.) is now fixed! - Silly Mistake!

8

noooooooooooo

as russell peters says, “be a man” :slight_smile:

write your queries and list all the columns, and only the columns, that you need – with aliases where necessary

9

Excellent, OK, “I’ll be a man” as you say and write my queries out in full!