PHP, Mysql query different tables in one function

Question

function getDepartmentAndCondition($dep, $userid, $cond) {
    $result = mysql_query("SELECT * FROM department WHERE ID='$dep'");
    while($row = mysql_fetch_array($result))
    {
        $DepConInfo['Department'] = $row['Department'];
    }
    $userName = mysql_query("SELECT * FROM users WHERE FacebookID = '$userid'") or die ("<hr>error in SQL query: " . mysql_error() . "<hr>");
    while($row = mysql_fetch_array($username)) {
        $DepConInfo['Name'] = $row['name'];

    }

    $result2 = mysql_query("SELECT * FROM condition WHERE ID= '$cond' ")
    or die("<hr>error in SQL query: " . mysql_error() . "<hr>");
    while($row2 = mysql_fetch_array($result2))
    {
        $DepConInfo['Condition'] = $row2['Condition'];
    }


    return $DepConInfo;

}

$dep, $userid, and $cond are all ints. the first one $DepConInfo['Department'] is returning the right string, but the other two fail with the error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ...

ok I rewrote the function



 function getCondition($cond) {
    $query = "SELECT * FROM condition WHERE ID = '$cond' "; 
    $sql = mysql_query($query);

    if (!$sql) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);    
}
    while($row2 = mysql_fetch_array($sql))
       {
       $condition = $row2['Name'];
       }
    return $condition;
}

but I'm still getting an error:
Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition WHERE id = '1'' at line 1 Whole query: SELECT * FROM condition WHERE ID = '1'

the table "condition" has two columns "ID" and "Name".

are you sure you have the rows available for your condition in the table on database. — footy
– footy, Commented Jan 25, 2012 at 7:06

Alex · Accepted Answer · 2012-01-25 07:06:34Z

3

while($row = mysql_fetch_array($username)) {

PHP is case-sensitive: you have $username with wrong caps - should be $userName

Additionally, based on your naming convention in the first and third queries

$DepConInfo['Name'] = $row['name'];

is probably incorrect and should be capitalized as $row['Name']

answered Jan 25, 2012 at 7:06

Alex

313 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sidneydobber · Accepted Answer · 2012-01-26 07:44:08Z

0

function getDepartment($dep) { 
    $sql = "SELECT * FROM department WHERE ID = '$dep'"; 
    $result = mysql_query($query); 
    if (!$result) { 
        echo 'Could not run query: ' . mysql_error();
        exit;
    $row = mysql_fetch_row($result);
    $department = $row['Department'];
    }
    return $department;
}

function getName($userid) { 
    $sql = "SELECT * FROM users WHERE FacebookID = '$userid'"; 
    $result = mysql_query($sql); 
    if (!$result) { 
        echo 'Could not run query: ' . mysql_error();
        exit;
    $row = mysql_fetch_row($result);
    $user_name = $row['Name'];
    }
    return $username;
}

function getCondition($cond) { 
    $sql = "SELECT * FROM condition WHERE id = '$cond'"; 
    $result = mysql_query($sql); 
    if (!$result) { 
        echo 'Could not run query: ' . mysql_error();
        exit;
    $row = mysql_fetch_row($result);
    $condition = $row['Condition'];
    }
    return $condition;
}

$department = getDepartment($dep);
$username = getName($userid);
$condition = getCondition($cond);

I'm writing this from my head so I did not test it, but it should work or at least get you on your way. If not let me know. Mind capitalization using caps in your dbase table and column names can make things more confusing. Use $sql to store your query, use $result to store the result. This is more descriptive. Good luck!

edited Jan 26, 2012 at 7:44

answered Jan 25, 2012 at 7:16

sidneydobber

2,9102 gold badges25 silver badges32 bronze badges

3 Comments

sidneydobber Over a year ago

This is an inderect answer to your problem, but this style will also help you prevent and solve problems in the future.

Tyler Lane Over a year ago

ok so i rewrote the function <code>function getCondition($cond) { $query = "SELECT * FROM condition WHERE id = '$cond' "; $sql = mysql_query($query); if (!$sql) { $message = 'Invalid query: ' . mysql_error() . "\n"; $message .= 'Whole query: ' . $query; die($message); } while($row2 = mysql_fetch_array($sql)) { $condition = $row2['Name']; } return $condition; }</code>

sidneydobber Over a year ago

There is a great book that I have read which teaches coding conventions and best practices. This helped me out a lot. It's called 'Clean Code' and it's a must read for every coder. Clean Code on Amazon

Collectives™ on Stack Overflow

PHP, Mysql query different tables in one function

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related