CODEWITHSUNDEEP
php
user1017566
user1017566

Reputation: 39

PHP echoing ambiguous names from multiple tables

I just realized my mistake! I used the table jokecategory twice... learning PHP is a humbling experience...

Thank you for your help, so far so good!

Sorry I didn't paste in the SQL, but your advice helped nonetheless. I modified the SQL query to use "AS", however I think I mixed something up with the "category" table. Can't figure out what, but it's not allowing me to search based on category now. Any ideas? I am receiving the following error:

Error: Not unique table/alias: 'jokecategory'

SQL query:

    $select = 'SELECT DISTINCT joke.id, joke.joketext, author.id AS author_name, author.name AS author_name, jokecategory.jokeid AS cat_jokeid, jokecategory.categoryid AS joke_catid, category.id AS cat_id, category.name as cat_name'; 
$from   = ' FROM joke, author, jokecategory, category'; 
$where = ' WHERE joke.authorid = author.id AND joke.id = jokecategory.jokeid AND jokecategory.categoryid = category.id';


$aid = $_POST['aid']; 
if ($aid != '') { // An author is selected
    $where .= " AND authorid='$aid'";
}

$cid = $_POST['cid']; 
if ($cid != '') { // A category is selected
    $from .= ', jokecategory'; 
    $where .= " AND joke.id=jokecategory.jokeid AND categoryid='$cid'";
}

$tid = $_POST['tid'];
if ($tid != '') { // A theme is selected
    $from .= ', joketheme';
    $where .= " AND joke.id=joketheme.jokeid AND themeid='$tid'";
}

$gfid = $_POST['gfid'];
if ($gfid != '') { // A region is selected
    $from .= ', jokegeofocus';
    $where .= " AND joke.id=jokegeofocus.jokeid AND geofocusid='$gfid'";
}

$searchtext = $_POST['searchtext']; 
if ($searchtext != '') { // Some search text was specified
    $where .= " AND joketext LIKE '%$searchtext%'";
    }

?>

Upvotes: 2

Views: 96

Answers (2)

uncreative
uncreative

Reputation: 1446

this can be accomplished with slight modifications to the sql in $jokes

SELECT j.name as jokename, a.name as authorname 
FROM joke j, author a 
WHERE j.author_id = j.id

You would then be able to reference joke's name column via $joke['jokename'] and author's name column via $joke['authorname']

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270647

We don't see your SQL query, but you should be supplying column aliases for ambiguous columns with the AS keyword, as in:

SELECT 
  t1.name AS t1name,
  t2.name AS t2name
FROM
  t1 
  JOIN t2 ON t1.id = t2.id

These would then be accessed in a fetched row by their aliases:

while ($joke = mysql_fetch_array($jokes)) { 
  echo "Table 1 name: {$joke['t1name']}\n";
  echo "Table 2 name: {$joke['t2name']}\n";
}

A further suggestion (though again since we don't see the SQL it may be moot), is to be explicit about which columns you select.

Rather than doing 

SELECT t1.*, t2.*

a better practice is to explicitly list the columns you need:

SELECT t1.name, t2.id, t2.category .... t2.whatever

Upvotes: 1

Related Questions