MySQLi - lookup value, return other value in same row

[Armitage2k]

Hi there,

I am struggling with something that I refer to as a VLOOKUP() but not in Excel, instead in MySQLi.

Basically what I want to do is to take the value that was submitted by my form (variable: $property), look it up in my table (the record is unique), and once found return the value from column “Email” of the same entry.

Can something like this be done with PHP / MySQLi?

Thank you,
A2k

[JimL]

Definitly, to look up data and get related data is pretty much why we use databases in the first place

[php]<?php

$property = isset($_POST[‘property’]) ? $_POST[‘property’] : null;

if ($property) {
$mysqli = new mysqli(“localhost”, “my_user”, “my_password”, “world”);

if (mysqli_connect_errno()) {
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}

if ($stmt = $mysqli->prepare("SELECT email FROM user WHERE property=?")) {
	$stmt->bind_param("s", $property);
	$stmt->execute();

	$stmt->bind_result($email);
	$stmt->fetch();

	printf("The email connected to property %s is: %s", $property, $email);

	$stmt->close();
}

$mysqli->close();

}[/php]

From the docs: http://php.net/manual/en/mysqli.prepare.php

[Armitage2k]

What a rock star, thanks a lot

Let me try and analyze this a little, if there is something I am not quite clear about I will come back and ask.

Thanks a lot Jim!

[Armitage2k]

Hi again,

I played around with my script for a bit and changed to using sessions instead. Now my initial issue remains, although what I want to do now is to grab the $myusername variable from the session, match to the GPA_properties table and return me the value of columns “Email”, “Address_1”, “Address_2”, etc.

This obviously means that I have to query the database EACH time I want to have one value returned. Is there any way that I just query one time (meaning, only write one string) to get all of those values returned?

Thanks,
A2k

[JimL]

Separate the columns you want with commas, you can aldo use an adterisk (*) to get all columns.

I would consider storing all the user columns in session.

Or at least add user id. Looking up by id in the database is considerably faster than by a text field.

[Armitage2k]

I separated the columns and works fine, I will give this a go with the ID, even though my table only has around 10 columns and no more than 50 entries (Which shouldnt make much of a difference.

Thanks!
A2k