Lyleyboy Posted August 18, 2009 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Daniel0 Posted August 18, 2009 Share Posted August 18, 2009 I don't quite understand your problem. Could you elaborate a bit or perhaps show some (pseudo)code? Quote Link to comment Share on other sites More sharing options...
mikesta707 Posted August 18, 2009 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Catfish Posted August 18, 2009 Share Posted 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). Quote Link to comment Share on other sites More sharing options...
Lyleyboy Posted August 18, 2009 Author Share Posted 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; } ?> Quote Link to comment Share on other sites More sharing options...
mikesta707 Posted August 18, 2009 Share Posted 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? Quote Link to comment Share on other sites More sharing options...
Lyleyboy Posted August 18, 2009 Author Share Posted 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' Quote Link to comment Share on other sites More sharing options...
mikesta707 Posted August 18, 2009 Share Posted August 18, 2009 the login credentials for the discover_discover database may be different than the credentials for the other database. Quote Link to comment Share on other sites More sharing options...
Lyleyboy Posted August 18, 2009 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
mikesta707 Posted August 18, 2009 Share Posted 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? Quote Link to comment Share on other sites More sharing options...
Lyleyboy Posted August 18, 2009 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
mikesta707 Posted August 18, 2009 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Lyleyboy Posted August 18, 2009 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
mikesta707 Posted August 18, 2009 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Lyleyboy Posted August 18, 2009 Author Share Posted 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); } Quote Link to comment Share on other sites More sharing options...
Lyleyboy Posted August 18, 2009 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Lyleyboy Posted August 18, 2009 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted August 18, 2009 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Lyleyboy Posted August 18, 2009 Author Share Posted August 18, 2009 Not sure how but thanks. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.