Member Avatar for heshanm

This is my 2 tables.

account_details (account_number, nic, full_name, phone_number, address, gender, date_of_birth,__)

account (account_number, name_with_initials,account_type, fd_period,__)

I want to select records in both the tables. This is my SQL line.

$query ="SELECT account_details. nic,full_name,phone_number,address,gender,date_of_birth,account.name_with_initials,account_type,fd_period". "FROM account_details,account". "WHERE account_details.account_number=account.account_number";

The following error occurs.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.account_number=account.account_number' at line 1

  • 5 Contributors
  • 5 Replies
  • 184 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by Anuradha Mandal

Dani AI

Generated

Two separate problems are visible in the thread: a malformed SQL string (caused by how the PHP pieces were concatenated) and a missing filter to return a single account. The original PHP concatenation glued tokens together (for example the SELECT list and the FROM/WHERE keywords), which produces syntax errors like the one shown. was on the right track about spacing, and ’s use of table aliases is good practice — but an explicit WHERE that compares the joined keys only performs the join; it does not restrict results to a single account value.

Prefer an explicit JOIN plus a parameterized WHERE to both fix the syntax and return only the relevant row. Example (PDO) pattern:

$stmt = $pdo->prepare(
  'SELECT ad.nic, ad.full_name, ad.phone_number, ad.address, ad.gender, ad.date_of_birth,
          a.name_with_initials, a.account_type, a.fd_period
   FROM account_details AS ad
   JOIN account AS a ON ad.account_number = a.account_number
   WHERE ad.account_number = :acct
   LIMIT 1'
);
$stmt->execute([':acct' => $acct]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);

Troubleshooting checklist: (1) echo the final SQL string and paste into mysql to see the exact syntax error; missing spaces at string boundaries are a common cause; (2) confirm the filter variable isn’t empty or mis-typed (an empty WHERE value will effectively return many rows); (3) use prepared statements to prevent SQL injection; (4) use a single DB connection unless the tables are on different servers/databases (so ’s two-connections suggestion is unnecessary in the usual case). Following ’s aliasing and switching to a parameterized JOIN+WHERE will solve both the syntax error and the “all records” symptom.

Recommended Answers

All 5 Replies

Member Avatar for digwnews

May be

$query ="SELECT account_details. nic,full_name,phone_number,address,gender,date_of_birth,account.name_with_initials,account_type,fd_period"." FROM account_details,account"." WHERE account_details.account_number=account.account_number";
Member Avatar for dharma117 
select *from account_details ad,account ac
where ad.account_number=ac.account_number
Member Avatar for IIM 
$query ="SELECT ad.nic,ad.full_name,ad.phone_number,ad.address,ad.gender,ad.date_of_birth,a.name_with_initials,a.account_type,a.fd_period FROM account_details ad,account a WHERE ad.account_number=a.account_number";

In this code i have made alias of table account_details as "ad" and for account as "a".
And your error was that you have to write the name of fields along with table from which they are coming.

Member Avatar for heshanm

@ IIM; I tried your query in my coding. But it retrieves all the records irrespective of the account number. I want to display ONLY the relevant record set?

Member Avatar for Anuradha Mandal

You will need two different connections for two different tables.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.