PHP - Get second column value from primary key on same row

Question

I have two tables in one database.

id | title
---------           // table name is tableone
1  |  a
2  |  b
3  |  c
4  |  d
5  |  a
6  |  b
7  |  c
8  |  d

for the first table the primary key is the ID column. from this table I am using PHP to create a HTML table which works fine.

The second table

id | version
---------
a  |  1
b  |  3
c  |  6
d  |  7

in the while loop for the first table I have predefined the title value as

$title = $id['title'];

within the while loop i am using another while loop yet this time I am trying to use the predefined title to access the matching id's corresponding version row value on the second table using :

mysql_query("SELECT version FROM tableone WHERE ID=".$title)

however i am hinted with the error

Unknown column 'a' in 'where clause'

what is the problem and how can i fix it ?

Welcome to Stack Overflow! If you can avoid it, please don't use the `mysql_*` functions, they are no longer maintained and community has begun the [deprecation process](http://goo.gl/KJveJ) . Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you cannot decide, [this article](http://goo.gl/3gqF9) will help to choose. If you want to learn, [here is a good PDO-related tutorial](http://goo.gl/vFWnC). — vascowhite, Oct 12 '12 at 21:37

score 2 · Accepted Answer · edited May 23 '17 at 12:27

2

You should must enclose the $title in quotes like this:

mysql_query("SELECT version FROM tableone WHERE ID='".$title."'")

If you have user supplied input, you should make sure that your code is secure for all cases.

In this post are some good informations about that: https://stackoverflow.com/questions/3714107/php-mysql-security-checklist-the-definitive-practical-guide

edited May 23 '17 at 12:27

Community

1
1

answered Oct 12 '12 at 21:36

Thomas Berger

1,860
13
26

1

@YusafKhaliq That checklist linked to in this answer is rather out of date now, use this instead http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php – vascowhite Oct 12 '12 at 21:45
1

I'll repeat my comment for you as you didn't mention it in your answer. If you can avoid it, please don't use the `mysql_*` functions, they are no longer maintained and community has begun the [deprecation process](http://goo.gl/KJveJ) . Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you cannot decide, [this article](http://goo.gl/3gqF9) will help to choose. If you want to learn, [here is a good PDO-related tutorial](http://goo.gl/vFWnC). – vascowhite Oct 12 '12 at 21:47
Hey @vascowhite if you've read my profile description I'm new to php and mysql, this was just self taught practise for me, and i don't fully understand web terminology, but from what ive read and youve commented, are you saying i should learn PDO or MysQLi instead,and which would you personally recommend learning? – Yusaf Khaliq Oct 12 '12 at 21:54
@YusafKhaliq I haven't looked at your profile. I would definitely recommend learning PDO, follow the link in my comment for an easy to follow tutorial. The mysql_ functions will disappear soon so its a waste of time learning them. You'll be glad you put the effort into PDO. Good luck. – vascowhite Oct 12 '12 at 21:59
and one more Qs to use MySQLi can i still use the old mysql database on cpanel? – Yusaf Khaliq Oct 12 '12 at 21:59
@YusafKhaliq You don't have to change your database to use PDO or mysqli. – vascowhite Oct 12 '12 at 22:00
PDO is your new interface to your mysql database. If the database server updates (it does from time to time), PDO goes together with that. The `mysql_*` functions have stopped with Mysql Server 5.1 which is already some time ago. It's likely you have a newer database server than that. Just for your info so that you can see how outdated that already today is. – hakre Oct 12 '12 at 22:06

score 1 · Answer 2 · answered Oct 12 '12 at 21:42

1

Instead of doing a separate query within the loop, it's likely to be better to get all the information with one query using a join:

SELECT t1.id AS id, title, version
FROM tableone t1
JOIN tabletwo t2 ON t1.title = t2.id

answered Oct 12 '12 at 21:42

Barmar

741,623
53
500
612

PHP - Get second column value from primary key on same row

2 Answers2