Mysql query into an array

Lyleyboy · August 18, 2009

Hi all,

I have been advised that an array is the way to go. My original question involved queries from different databases.

My question is how do I run a mysql query to return two fields the put those into an array so that they remain linked.

I can then work through the array and update the next table. I have to confess to being useless with arrays and in fact have never used one.

Daniel0 · August 18, 2009

I don't quite understand your problem. Could you elaborate a bit or perhaps show some (pseudo)code?

mikesta707 · August 18, 2009

I don't know what you mean by "remain linked" but to get two columns from a table, and store them into an array you can do something like

$sql = "SELECT col1, col2 from table where something=something_else";
$query = mysql_query($sql);

$row = mysql_fetch_assoc($query);

echo $row['col1'] . "<br />;
echo $row['col2'] . "<br />;

that would output the two columns from the first row your query returned. to go through all the rows your query returns you can do something like

$sql = "SELECT col1, col2 from table where something=something_else";
$query = mysql_query($sql);

while($row = mysql_fetch_assoc($query)){
     echo $row['col1'] . "<br />;
     echo $row['col2'] . "<br />;
}

you can also use the mysql_fetch_array function: http://us2.php.net/manual/en/function.mysql-fetch-array.php

here is the page about mysql_fetch_assoc: http://us2.php.net/manual/en/function.mysql-fetch-assoc.php

and another function, that returns an array with only number indices: http://us2.php.net/manual/en/function.mysql-fetch-row.php

Hope that helps

Catfish · August 18, 2009

You could also use one field from the database as the array key and the other field from the database as the array value:

<?php
$sql = "SELECT col1, col2 from table where something=something_else";
$query = mysql_query($sql);

$row = mysql_fetch_assoc($query);

$assocArray[$row['col1']] = $row['col2'];

foreach ($assocArray as $key => $value)
echo $key." -> ".$value."<br />;
?>

In this way, your values from the database remain "linked" in the array. You can then also store multiple linked values in the array and loop through them, search them, remove duplicates etc using array functions (refer to php manual).

Lyleyboy · August 18, 2009

Ok, I have to admit to being terrible at describing issues. My doctor recently told me this.

<?php
//messaging_group($_POST['group_dd'], $subject, $body);
function messaging_all($subject, $body)
{
  //Get my user
  $username = $_SESSION['username'];
  
  //Setup my date
  $sent_date = date('U');
   
    //mysql_select_db("discover_discovery");
    $query = "SELECT username,email FROM users WHERE allowed='on' and email is not null";
    $result = mysql_query($query) or die(mysql_error());
      while($row = mysql_fetch_array($result)){
        $to = $row['username'];
        $email_to = $row['email'];
        mysql_select_db("discover_messaging") or die(mysql_error());;
        mysql_query("INSERT INTO messages (msg_to, msg_from, msg_subject, msg_body, read_flg, sent_date) VALUES ('$to', '$username', '$subject' , '$body', 'off', '$sent_date')") or die(mysql_error());
        mysql_select_db("discover_discover") or die(mysql_error());;
        
        //Send them an email to notify of a new message
        $email_subject = "[PRIVATE MESSAGE] You have a new message";
        $email_body = "Hi, " . $to . " You have a new message, Log in to view it.";
        mail($email_to,$email_subject,$email_body,"From: Discovery ESU\n") or die($error = 1);
      }
}
?>

The issue I think is that im switching databases part way through. The weird thing is that the below script works perfectly.

<?php
//messaging_group($_POST['group_dd'], $subject, $body);
function messaging_group($to, $subject, $body)
{
  //Get my user
  $username = $_SESSION['username'];
  
  //Setup my date
  $sent_date = date('U');
  
  
  //Setup my queries
  switch ($to) {
    case "s_admin":
        $qry = "SELECT username,email FROM users WHERE s_admin='on'";
        break;
    case "admin":
        $qry = "SELECT username,email FROM users WHERE admin='on'";
        break;
    case "mods":
        $qry = "SELECT username,email FROM users WHERE moder='on'";
        break;
    case "bronze":
        $qry = "SELECT username,email FROM users WHERE bronze='on'";
        break;
    case "silver":
        $qry = "SELECT username,email FROM users WHERE silver='on'";
        break;
    case "gold":
        $qry = "SELECT username,email FROM users WHERE gold='on'";
        break;
    default:
        $qry = "SELECT username,email FROM users WHERE night='$to'";
        break;
}
  

    
    
    
    
    
    //mysql_select_db("discover_discovery");
    $query = $qry;
    $result = mysql_query($query) or die(mysql_error());
      while($row = mysql_fetch_array($result)){
        $to = $row['username'];
        $email_to = $row['email'];
        mysql_select_db("discover_messaging");
        mysql_query("INSERT INTO messages (msg_to, msg_from, msg_subject, msg_body, read_flg, sent_date) VALUES ('$to', '$username', '$subject' , '$body', 'off', '$sent_date')") or die($error = 1);
        mysql_select_db("discover_discover");
        
        //Send them an email to notify of a new message
        $email_subject = "[PRIVATE MESSAGE] You have a new message";
        $email_body = "Hi, " . $to . " You have a new message, Log in to view it.";
        mail($email_to,$email_subject,$email_body,"From: Discovery ESU\n") or die($error = 1);
      }
       
  return $error;
}
?>

mikesta707 · August 18, 2009

are you getting any mysql errors? I still don't really understand what your problem is. What is supposed to happen and what is actually happening?

Lyleyboy · August 18, 2009

As I say, bad at explaining. Apologies.

I'm doing a messaging script for a community site I'm building. The write new page is just a form. In the "To" bit you can either choose a single user, a group or everyone.

When the script is submitted either one of the functions is called.

The below function is for the group message and works perfectly.

<?php
//messaging_group($_POST['group_dd'], $subject, $body);
function messaging_group($to, $subject, $body)
{
  //Get my user
  $username = $_SESSION['username'];
  
  //Setup my date
  $sent_date = date('U');
  
  
  //Setup my queries
  switch ($to) {
    case "s_admin":
        $qry = "SELECT username,email FROM users WHERE s_admin='on'";
        break;
    case "admin":
        $qry = "SELECT username,email FROM users WHERE admin='on'";
        break;
    case "mods":
        $qry = "SELECT username,email FROM users WHERE moder='on'";
        break;
    case "bronze":
        $qry = "SELECT username,email FROM users WHERE bronze='on'";
        break;
    case "silver":
        $qry = "SELECT username,email FROM users WHERE silver='on'";
        break;
    case "gold":
        $qry = "SELECT username,email FROM users WHERE gold='on'";
        break;
    default:
        $qry = "SELECT username,email FROM users WHERE night='$to'";
        break;
}
  

    
    
    
    
    
    //mysql_select_db("discover_discovery");
    $query = $qry;
    $result = mysql_query($query) or die(mysql_error());
      while($row = mysql_fetch_array($result)){
        $to = $row['username'];
        $email_to = $row['email'];
        mysql_select_db("discover_messaging");
        mysql_query("INSERT INTO messages (msg_to, msg_from, msg_subject, msg_body, read_flg, sent_date) VALUES ('$to', '$username', '$subject' , '$body', 'off', '$sent_date')") or die($error = 1);
        mysql_select_db("discover_discover");
        
        //Send them an email to notify of a new message
        $email_subject = "[PRIVATE MESSAGE] You have a new message";
        $email_body = "Hi, " . $to . " You have a new message, Log in to view it.";
        mail($email_to,$email_subject,$email_body,"From: Discovery ESU\n") or die($error = 1);
      } 
  return $error;
}
?>

The below code is the function for sending to everyone which doesn't work.

<?php
//messaging_group($_POST['group_dd'], $subject, $body);
function messaging_all($subject, $body)
{
  //Get my user
  $username = $_SESSION['username'];
  
  //Setup my date
  $sent_date = date('U');
   
    //mysql_select_db("discover_discovery");
    $query = "SELECT username,email FROM users WHERE allowed='on' and email is not null";
    $result = mysql_query($query) or die(mysql_error());
      while($row = mysql_fetch_array($result)){
        $to = $row['username'];
        $email_to = $row['email'];
        mysql_select_db("discover_messaging") or die(mysql_error());;
        mysql_query("INSERT INTO messages (msg_to, msg_from, msg_subject, msg_body, read_flg, sent_date) VALUES ('$to', '$username', '$subject' , '$body', 'off', '$sent_date')") or die(mysql_error());
        mysql_select_db("discover_discover") or die(mysql_error());;
        
        //Send them an email to notify of a new message
        $email_subject = "[PRIVATE MESSAGE] You have a new message";
        $email_body = "Hi, " . $to . " You have a new message, Log in to view it.";
        mail($email_to,$email_subject,$email_body,"From: Discovery ESU\n") or die($error = 1);
      }
}
?>

The only real change is the fact that I'm not passing a 'To' as it's going to everyone.

The script seems to run the first insert into then stop. It doesn't send the email notification and then stops.

On screen error is

Access denied for user '***USER***'@'localhost' to database 'discover_discover'

mikesta707 · August 18, 2009

the login credentials for the discover_discover database may be different than the credentials for the other database.

Lyleyboy · August 18, 2009

The credentials are the same.

The script does a query from the users table in the discover database then inserts one record into the messages table in the messaging database then stops and reports the error. Weird huh

mikesta707 · August 18, 2009

hmm... yeah since you basically do the same thing in the script that works, it should work perfectly fine in the non-working script. The user account you are using has the necessary privileges right?

Lyleyboy · August 18, 2009

All the same. Really weird isn't it?

Why does the group function work and the all function not. I left the all till last as I thought it would be the easiest. No such luck.

Any idea's for a better way round it? Other than moving the table?

mikesta707 · August 18, 2009

well why exactly do you switch to that database? I don't see any queries afterwards so try just removing that line.

Lyleyboy · August 18, 2009

I have most of my data stored in the discover table. My DB's are restricted to 100MB by my host.

Since the messaging is likely to get quite big with over 100 users and if the message is sent to all then there will be 100 records for just that message.

I have planned to user discover for all the config things, the photo gallery bit (which is already there and running), the users setups and my CMS and use the second messaging for the messaging and forums.

Kind of wishing I hadn't now. This has killed my tiny little mind. :facewall: :suicide:

mikesta707 · August 18, 2009

ok, but in that particular script, after you switch to the discover database, what do you do once switched?

by the way you aren't switching to a discover table, you are switching databases. IDK if this has something to do with it, or if you are confusing databases and tables

Lyleyboy · August 18, 2009

No only my fingers confusing tables and databases. I've been banging my head for a while.

Let me try to step through it.

$query = "SELECT username,email FROM users WHERE allowed='on' and email is not null";  //Nothing clever here just a query. Even tried it without the email is not null
    $result = mysql_query($query) or die(mysql_error());
      while($row = mysql_fetch_array($result)){
        $to = $row['username'];   //Finds the usernames and emails and stores into vars
        $email_to = $row['email'];   //Finds the usernames and emails and stores into vars
        mysql_select_db("discover_messaging") or die(mysql_error());  //Change to the messaging DB to post the message there
        mysql_query("INSERT INTO messages (msg_to, msg_from, msg_subject, msg_body, read_flg, sent_date) VALUES ('$to', '$username', '$subject' , '$body', 'off', '$sent_date')") or die(mysql_error());   //Post the message
        mysql_select_db("discover_discover") or die(mysql_error()); //Switch back to discover to continue the query
        
        //Doesnt matter dont get this far!!!!!!

        //Send them an email to notify of a new message
        $email_subject = "[PRIVATE MESSAGE] You have a new message";
        $email_body = "Hi, " . $to . " You have a new message, Log in to view it.";
        mail($email_to,$email_subject,$email_body,"From: Discovery ESU\n") or die($error = 1);
      }

Lyleyboy · August 18, 2009

removed the swithc back to discover and got the same issue exactly except it doesn't report the error just nothing.

Don't know if you have seen this but when there is an error the page won't completely load. In this case the background colour is not being set. That's all there is to tell me that there is a problem.

Lyleyboy · August 18, 2009

FIXED IT. It turned out that some of the email fields are null but the IS NOT NULL was returning them anyway. How shoddy is that?

May have to go with >1 rather than is not null

Apologies for wasting time. Thanks for your help.

roopurt18 · August 18, 2009

IS NOT NULL does not return null fields.

They are either empty strings in the database or you are some how converting them from empty strings to NULL values.

Lyleyboy · August 18, 2009

Not sure how but thanks.

Sign In

Mysql query into an array

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information