[RESOLVED] Shortcut to query a database

[yoki]

Hi, I need to query different tables in order to retrieve the ID of each value. I'm doing this like this:

mysql_select_db("abstracts");
$query = "SELECT * FROM Province_State WHERE 1 AND Prov_ST = '".$Prov_ST."' ";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
$Prov_ST_ID = $row["Prov_ST_ID"];
}

$query = "SELECT * FROM Country WHERE 1 AND Prov_ST = '".$Country."' ";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
$Country_ID = $row["Country_ID"];
}

Is there a faster way of doing this then doing so many steps just to get a simple value?

[jazz_snob]

If you only want the ID of a record, make your query return just an ID:

$query = "SELECT id 
                 from Province_State 
                 WHERE Prov_ST='".mysql_real_escape_string( $Prov_ST ) ."'";

if( !( $res = mysql_query( $query ) ) ){
    die( mysql_error() );
}
list( $Prov_ST_ID ) = mysql_fetch_row( $res );

You could make this a function if it would be useful:

function find_id( $table, $column, $value ) {
  $query = "SELECT id 
                   from $table 
                   WHERE $column = '".mysql_real_escape_string($value)."'";
  if( !( $res = mysql_query( $query ) ) ){
    die( mysql_error() );
  }
  list( $id ) = mysql_fetch_row( $res );
  return $id;
}

[yoki]

Thanks!!! That's exactly what I was looking for 🙂