mysql-php problem

[Mumlebumle]

So i started on a project and not within long, i hit a wicked wall. The upper part works fine, i get the correct $pilotcorpid, however it doesnt seem to query the 2nd time, it just uses the variable from the first time.

 

I have the information in the database, i just need to pick em out right.

 

<?php
include 'config.php'; //database config
include 'opendb.php'; //connect database

$search = @$_GET['q'] ; //getting a name from a form

$result = mysql_query("SELECT * FROM kb3_pilots WHERE plt_name='$search'")or die(mysql_error()); //here i choose to check in the kb3_pilots table, the plt_name column for the name written in the form earlier.
$pilotcorpid=mysql_result($result,"plt_crp_id"); //since i found the guy in the query, i need info from that column, the plt_crp_id.

$resultcorp = mysql_query("SELECT * FROM kb3_corps WHERE crp_id='$pilotcorpid'")or die(mysql_error());  //now we query the kb3_corps table to check if the corp exists.
$pilotcorpname=mysql_result($resultcorp,"crp_name"); //Because it did, i will now pull out the name

echo "TEST: Pilot Id: $pilotcorpid and corp name: $pilotcorpname";

include 'closedb.php';
?>

 

If somebody also have a big php/mysql ebook which explains commands great, that would be awesome.

 

 

Best regards,

Mumlebumle

[wildteen88]

No need for those two queries. You only need to use one query, a basic join

 

$query = "SELECT p.plt_crp_id, 
                 c.crp_name 
          FROM kb3_pilots p, 
               kb3_corps c 
          WHERE p.plt_name='$search' AND p.plt_crp_id = c.crp_id";

$result = mysql_query($query);

list($pilotcorpid, $pilotcorpname) = mysql_fetch_row($result);

echo "TEST: Pilot Id: $pilotcorpid and corp name: $pilotcorpname";

[kickstart]

Hi

 

Agree with the above point (although you probably want to use a LEFT OUTER JOIN if there might not be a matching record on the 2nd table).

 

However the problem you appear to have here is down to mysql_result. The 2nd parameter is the row number, whereas you seem to have specified the field name as the 2nd parameter.

 

All the best

 

Keith

[jcbones]

Try it this way: UN-TESTED

<?php
include 'config.php'; //database config
include 'opendb.php'; //connect database

$search = (isset($_GET['q'])) ? mysql_real_escape_string($_GET['q']) : NULL ; //getting a name from a form

if($search == NULL) {
echo 'You have not defined a search parameter.';
exit();
}
$sql = "SELECT a.*,b.* FROM kb3_pilots as a, kb3_corps as b WHERE a.plt_crp_id = b.crp_id AND a.plt_name = '$search'";
$result = mysql_query($sql)or die(mysql_error()); //here i choose to check in the kb3_pilots table, the plt_name column for the name written in the form earlier.
if(mysql_num_rows($result) > 0) {
while($row = mysql_fetch_assoc($result)) {
$pilotcorpid = $row['plt_crp_id'];
$pilotcorpname = $row['crp_name'];

echo "TEST: Pilot Id: $pilotcorpid and corp name: $pilotcorpname<br/>\n";
}
}
else {
echo 'There was no results returned from your search.';
}

include 'closedb.php';
?>

 

Resources:

PHP Manual

MySQL Manual

 

PS. I added some checks in there for added functionality.