/    Sign up×
Community /Pin to ProfileBookmark

[RESOLVED] Join

Copy linkTweet thisAlerts:
Oct 06.2010

i have 2 tables: “fga” and “directory” with a common field of “name”

i have a search form that is always showing error: “Couldn’t execute query”

[code=html]<form name=”form” action=”billing.php” method=”get”>
<input type=”text” name=”q” />
<input type=”submit” name=”Submit” value=”Search” />
</form>[/code]

[code=php]<?php
include(“config.php”);
$connection=mysql_connect($hostname,$user,$pass)or die (“Failed to connect.”);
mysql_select_db($dbname,$connection) or die (“Cannot connect to DB”);

$var = @$_GET[‘q’];
$q=$_REQUEST[‘q’];
$result = mysql_query(“SELECT * FROM fga JOIN directory ON fga.name=directory.name WHERE name LIKE “%$q%””) or die(“Couldn’t execute query”);?>[/code]

to post a comment
PHP

3 Comments(s) ↴

Copy linkTweet thisAlerts:
@BIOSTALLOct 06.2010 — Hi beylah,

Change your query to this:

[code=php]$result = mysql_query("SELECT * FROM fga JOIN directory ON fga.name=directory.name WHERE fga.name LIKE "&#37;$q%"") or die("Couldn't execute query: ".mysql_error());?>[/code]

I've made two changes:

Because the 'name' field is common in both tables you have to declare which table to reference also in the WHERE statement. Notice how I've prefixed this with the table fga now.

I have also amended the die() output. mysql_error() will tell you exactly what the error. In this case it would have said something like 'Column name is ambiguous'.

Hope that helps ?
Copy linkTweet thisAlerts:
@beylahauthorOct 07.2010 — here is the error (thanks for showing me that - i will use it all the time now)

[B]Couldn't execute query: Column 'name' in order clause is ambiguous[/B]

so i have changed the code to this - [B]i get the a blank table with the correct number of colums for the right amount of results but no text[/B]

[code=php]$sql = "SELECT fga.name AS fga_name, directory.floor AS directory_floor, directory.id AS directory_id, directory.phone AS directory_phone, directory.department AS directory_department, fga.collector AS fga_collector, fga.collectorext AS fga_collectorext FROM fga JOIN directory ON fga.name=directory.name WHERE fga.fga='$var' ORDER BY fga.name";
$result=mysql_query($sql) or die("Couldn't execute query: ".mysql_error());
while ($row=mysql_fetch_array($result)){
echo "<td class="search">$rows[fga_name]</td>";
echo '<td class="search"><a href="../'.$rows['directory_floor'].'.php?id='.$rows['directory_id'].'" target="_blank">mapit</a></td>';
echo "<td class="search">$rows[directory_phone]</td>";
echo "<td class="search">$rows[directory_department]</td>";
echo "<td class="search">$rows[fga_collector]</td>";
echo "<td class="search">$rows[fga_collectorext]</td>";
echo "</tr>";
}[/code]
Copy linkTweet thisAlerts:
@beylahauthorOct 07.2010 — ok i solved it - dumb mistake

in the while state i use "row"

and in the echo i used "rows"
×

Success!

Help @beylah spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.18,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...