Making fields in a row into individual variables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tarron
Forum Newbie
Posts: 13
Joined: Thu May 23, 2002 12:09 pm

Making fields in a row into individual variables

Post by tarron »

If I have a variable that matches field1 of a specific row in my database, how do I pull field2 and field3 from that row as variables to use in my php script?

I'm really new to this. I'm using php and ODBC to pull info from a small SQL database. I have a form that you type in a number, and when the form is submitted, I need the number to find its match in the database and then display that number, and the other two fields in its row. However, I'd like those other two fields to be able to be used as variables for another script that is further down in the php page... If that makes sense..

I'd appreciate any help.
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

Define the Database details HOST,USERNAME,USER_PASSWORD,DBNAME
then.

Code: Select all

$dblink = mysql_connect(HOST, USERNAME, USER_PASSWORD) or die("Unable to connect to the database.");
 
      $query = "SELECT field1, field2, field3 FROM tablename WHERE field1="$value"";
      $result = mysql_db_query(DBNAME, $query) or die("Unable to retrieve records from the database.");
 
      $dbrow = mysql_fetch_array($result);
      $field2 = $dbrowї"field2"];
      $field3 = $dbrowї"field3"];
  
      mysql_close($dblink);
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

The above will only work if you are using a MySQL database. To do this with a database that you connect to using ODBC you would need to do something similar to,

Code: Select all

$query = "SELECT field1, field2, field3 FROM tablename WHERE field1='$value'"; 
$result = odbc_exec($db_conn, $query) or die('Unable to retrieve records from the database.'); 

$dbrow = odbc_fetch_array($result); 
$field2 = $dbrowї'field2']; 
$field3 = $dbrowї'field3']; 
  
odbc_close($dblink);
(with all the connection stuff done before).

If you are using a MySQL database try this modified version of Wayne's code:

Code: Select all

@$dblink = mysql_connect(HOST, USERNAME, USER_PASSWORD) or die('Unable to connect to the database.');
@mysql_select_db(DBNAME) or die('Unable to select database');

$query = "SELECT field1, field2, field3 FROM tablename WHERE field1='$value'"; 
@$result = mysql_query($query) or die('Unable to retrieve records from the database.'); 

$dbrow = mysql_fetch_assoc($result); 
$field2 = $dbrowї'field2']; 
$field3 = $dbrowї'field3']; 

mysql_close($dblink);
Only a few slight modifications - mysql_db_query has been deprecated and shouldn't be used, changed mysql_fetch_array to mysql_fetch_assoc and it's easier to use single quotes within SQL statements than escaped double quotes (\").

Mac
tarron
Forum Newbie
Posts: 13
Joined: Thu May 23, 2002 12:09 pm

Post by tarron »

Thanks guys, I actually figured it out yesterday, here's what I used:

[
$query ="select * from PROJECTS where project_number= $project_number";

if(!($db = odbc_connect("timesheet", "sa", "sa")))
die(" Error Executing query $query");
if(!($result = odbc_do($db, $query)))
die("error executing this frickin query $query");
$project_name = odbc_result($result, project_name);
$project_client = odbc_result($result, project_client);
]
project_number in the $query is a variable gotten from a GET input from a previous php page.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You don't need to use the if statements you could just do,

Code: Select all

@$db = odbc_connect("timesheet", "sa", "sa") or die('Error info'); 
@$result = odbc_do($db, $query) or die('Error info');
Glad you got it sorted anyway.

Mac
Post Reply