Help selecting 2 rows from a single table

[maxwell_]

Hi

I am trying to select two rows from a single table.
One of the rows will be selected via a GET variable the other from a variable within the php code.

Currently I can select a single row using -

$username = $_GET['name'];
$query = mysql_query("SELECT * FROM users WHERE name = '$username'") or die(mysql_error()); 

The row above could be any of 20 users listed on a dropdown menu.
The second row is a Definite, this row will go along with the above everytime the form is submitted.
I thought I could simply create another variable within the script and use AND, however this doen't appear to be working

$username = $_GET['name'];
$adminuser = 'Administrator';

 $query = mysql_query("SELECT * FROM users WHERE name = '$username' AND name = '$adminuser'") or die(mysql_error()); 

Best regards Maxwel

[cahva]

Use OR instead of AND. BTW, using $_GET var in your query without escaping? I smell SQL injection coming 🙂

[maxwell_]

Hi

I changed AND to OR however I'm still only getting the email associated with the GET variable.

[Weedpacket]

(Obvious question: are you sure the username is Administrator?)
How are you using [font=monospace]$query[/font]? How many rows does it contain?

[maxwell_]

Yes the name is correct.
I just copied and pasted this from name column in phpmyadmin - Administrator
All of the other names in the name column are double barrelled like - James Jones
the other relevant column in the users table is - user_email

This is the full script using OR instead of AND as you suggested.

if(isset($_GET['report']) && isset($_GET['name'])) 
{ 
    $connect = mysql_connect("local", "online", "password"); 
    $connected = mysql_select_db("abonline_tutor", $connect); 

	$username = $_GET['name']; 
$report = $_GET['report'];
		$adminuser = 'Administrator';

if($connected or die(mysql_error())) 
{     
 $query = mysql_query("SELECT * FROM users WHERE name = '$username' OR name = '$adminuser'") or die(mysql_error());

    $result[0] = mysql_fetch_assoc($query); 	

    $to = $result[0]['user_email']; 
    $first_name = $result[0]['name'];         
    $from = '[e-mail]'; 
    $subject = 'Staff computer problem reported:' . ' ' . strtoupper($username);         
    $msg = "Dear $first_name,\r\n\r\n$report"; 

    if(mail($to, $subject, $msg, 'From:' . $from)) 
        echo "Message sent."; 
    else 
        echo "Error! Message not sent."; 
} 

} 

[Weedpacket]

        $result[0] = mysql_fetch_assoc($query);      

It's no surprise you only get one row from the result: you only ever ask for one. After you've got that one you need to look to see if there is another (and put it in [font=monospace]$result[1][/font].

if($second_user = mysql_fetch_assoc($query))
{
  $result[1] = $second_user;
}

PS: use of the MySQL extension in new development is not recommended.

[maxwell_]

Hi
Thanks for helping with this.
I tried the following code however now I get the Administrator email and not the one from the GET variable.

require_once('../../Connections/test.php'); 
ob_start(); 

error_reporting(E_ALL); 

if(isset($_GET['report']) && isset($_GET['name'])) 
{ 
    $connect = mysql_connect("local", "online", "password"); 
    $connected = mysql_select_db("abonline", $connect); 

	$username = $_GET['name']; 
    $report = $_GET['report'];
	$adminuser = 'Administrator';

if($connected or die(mysql_error())) 
{     
 $query = mysql_query("SELECT * FROM users WHERE name = '$username' OR name = '$adminuser'") or die(mysql_error());

    $result[0] = mysql_fetch_assoc($query);
	if($second_user = mysql_fetch_assoc($query))
{
  $result[1] = $second_user;         

        $to = $result[0]['user_email']; 
		$to = $result[1]['user_email'];
        $first_name = $result[0]['name']; 
		$first_name = $result[1]['name'];         

        $from = '[e-mail]'; 
        $subject = 'Staff computer problem reported:' . ' ' . strtoupper($username);         

        $msg = "Dear $first_name,\r\n\r\n$report"; 

    if(mail($to, $subject, $msg, 'From:' . $from)) 
        echo "Message sent."; 
    else 
        echo "Error! Message not sent."; 
} 

} }

Best regards Maxwell

[maxwell_]

Hi

I tried the following code and it is now working as required.
Although I'm sure there must be another way to acheive this involving less code.
Hopefully manage to sort it out once I get a bit more confidence with PHP.

Many thanks for your help.

require_once('../../Connections/abe.php'); 
ob_start(); 

error_reporting(E_ALL); 

if(isset($_GET['report']) && isset($_GET['name'])) 
{ 
    $connect = mysql_connect("local", "online", "password"); 
    $connected = mysql_select_db("abonline", $connect); 

	$username = $_GET['name']; 
    $report = $_GET['report'];
	$adminuser = 'Administrator';

if($connected or die(mysql_error())) 
{     
 $query = mysql_query("SELECT * FROM users WHERE name = '$username' OR name = '$adminuser'") or die(mysql_error());
	   //$query = mysql_query("SELECT * FROM users WHERE name = '$username'") or die(mysql_error()); 

    $result[0] = mysql_fetch_assoc($query);
	$to = $result[0]['user_email']; 
	$first_name = $result[0]['name']; 
	$from = '[e-mail]'; 
    $subject = 'Staff computer problem reported:' . ' ' . strtoupper($username);         
    $msg = "Dear $first_name,\r\n\r\n$report"; 

    if(mail($to, $subject, $msg, 'From:' . $from)) 
        echo "Message sent."; 
    else 
        echo "Error! Message not sent."; 
} 
if($second_user = mysql_fetch_assoc($query))
{
  $result[1] = $second_user;  

  $to = $result[1]['user_email']; 
		$first_name = $result[1]['name']; 
		$from = '[e-mail]'; 
        $subject = 'Staff computer problem reported:' . ' ' . strtoupper($username);         

        $msg = "Dear $first_name,\r\n\r\n$report"; 

    if(mail($to, $subject, $msg, 'From:' . $from)) 
        echo "Message sent."; 
    else 
        echo "Error! Message not sent."; 
} 
}

Best regards maxwell

[maxwell_]

Hi

I'm not sure whether to open a new thread for this as it has been some time since my last post.

My script currently selects the user from the GET variable $username = $_GET['name'];

and also the user associated with the $adminuser = 'Administrator';
However I now have multiple users with name Administrator of which only the first instance
in the table receives an email

I'm sure this requires a minnor adjustment to send an email to all users with name Administrator,
I have done lots of trial and error changing OR to AND
also tried GROUP BY
I also tried
SELECT * FROM users WHERE name = '$username' OR users.name = '$adminuser'

all with no success.

Best regards Maxwell

[Weedpacket]

Well, it's a new problem, so a new thread would be appropriate 🙂 But you'd use a loop to go through all the records returned by [man]mysql_query[/man] (see that page for an example) to collect all the addresses you want to send the email to. When sending multiple emails, it's better to use something more efficient than multiple calls to [man]mail[/man] (see which for a note on the subject).

Incidentally, did you know that the MySQL extension is discouraged for new development and is slated for deprecation? See the warning note at the top of the mysql_query page for more.

[maxwell_]

Hi

Thanks for that, simply enclosing the whole code block in a while loop did it.
And as you said mysqli is a more secure option which I will take on board.

Updated code

$query = mysql_query("SELECT * FROM users WHERE name = '$username' OR name = '$adminuser'") or die(mysql_error());
	   //Add query contents to array result[0], send first email        

       while($result[0] = mysql_fetch_assoc($query)){
		$to = $result[0]['user_email']; 
		$first_name = $result[0]['name']; 
		$from = '[e-mail]'; 
        $subject = 'Staff computer problem reported:' . ' ' . strtoupper($username);         

        $msg = "Dear $first_name,\r\n\r\n$report"; 

    if(mail($to, $subject, $msg, 'From:' . $from)) 
        echo "Message sent."; 
    else 
        echo "Error! Message not sent."; 
} 
if($second_user = mysql_fetch_assoc($query))
{
	//send second email to second item in in array result[1]
  $result[1] = $second_user;  

  $to = $result[1]['user_email']; 
		$first_name = $result[1]['name']; 
		$from = '[e-mail]'; 
        $subject = 'Staff computer problem reported:' . ' ' . strtoupper($username);         

        $msg = "Dear $first_name,\r\n\r\n$report"; 

    if(mail($to, $subject, $msg, 'From:' . $from)) 
        echo "Message sent."; 
    else 
        echo "Error! Message not sent."; 
} 
}

All best.

Maxwell