wookie

How would I use joins in this scenario? As I said, I'm way out of my depth now and have no idea on how to extend the query I have now. Wookie

Just reading through some other topics searching for an answer, would inner join help me out here? I've no idea i it will, just guessing and trying to get my head round it all. Wookie

I have a bridged Coppermine Gallery and Phpbb forum running from the same database, in the gallery people make comments on photo's members have uploaded...its a community thing. Now I have put an sql query together that counts those comments made by the members and displays the No of comments that member has made underneath their post count in the forum area of the site. Here is that query. $sql = "SELECT COUNT(*) as num_comments FROM `cpg14x_comments` WHERE author_id = '" . $row['user_id'] . "'"; $result = $db->sql_query($sql); $count_row = $db->sql_fetchrow($result); $comment = $count_row['num_comments']; $db->sql_freeresult($result); What I want to do is only count the comments made by members on other members photo's, not comments made on their own. At the moment the above query counts ALL comments made. Here are the three table and some field names of note that I think can be used to help me achieve this, although I lack the SQL knowledge to implement this query further than I have so far. Table name Field names, (Description) cpg14x_pictures owner_id (Example: 2) owner_name (Example: Wookie) pid (Picture id No, Example: 37) cpg14x_comments author_id (Example: 2) msg_author (Example: Wookie) pid (Picture id No, Example: 37) phpbb_user user_id (Example: 2) username (Example: Wookie) owner_id , author_id and user_id are all the same number. owner_name, msg_author and username are the same name pid, pid are the same number I want to be able to count comments from cpg14x_comments where the author_id = user_id from phpbb_users (I'm doing this part already with the above query), but (and this is the bit I have had no luck doing) At the same time, I want to check the pid from those comments against the tables cpg14x_pictures pid number and not include them is the final result if the pid is owned by the author_id. I hope I've given enough info for clarity's sake, can you help me take this query further? Many thanks in advance for any help offered. Wookie

Would there be another way of doing this as group_concat is restricted by default to 1024 characters which would be exceeded by my query result and hence truncated? Many thanks Wookie

Thanks very much I will try that, and will also look up the command to understand it too. Regards Wookie

The third party script I'm using is an autopost script that posts to a forum, this script can only run one query and post its results, hence why I'm making another table and condensing results to that. This is why I require all the urls in one field on one row. HTH Wookie

Sure you can.. $query1 = mysql_query("INSERT INTO stev1965_smf14.listing (topl) SELECT url FROM stev1965_smf14.details WHERE position = 0") or die("Error: ".mysql_error()); The above query makes many new rows, each with a "url" in the collumn "topl", I need all the url's to be in one field in one row if its possible. Regards Wookie

I want to select a whole column (from one table) and copy the results into one field in another table, I've tried using "INSERT INTO/SELECT" but this just creates many new rows in the second table, and I want it into one field...can this be done? Many thanks! Wookie

I have this script working, its an automated posting script for a bulletin board, it posts one set of information, I would like it to post around 30-40 sets of information where position = 0 as in the "WHERE" from the query below. The area where I would like this to show is in the "$msgBody = $query_results;" part, echo or print does not seem to work for $msgbody.I've gone round in circles this last few days attempting to write alternate queries and such. How can I get this script to produce more than just one result? <?php //These three files are needed for this script to work--adjust the path to suit your installation, a URL will not work. require_once('/home/stev1965/public_html/foru/SSI.php'); require_once('/home/stev1965/public_html/foru/Sources/Post.php'); require_once('/home/stev1965/public_html/foru/Sources/Subs-Post.php'); $query = mysql_query("SELECT cpmsubtitle FROM stev1965_smf14.cpg14x_potdp WHERE position = 0") or die("Error: ".mysql_error()); while($smffetch=mysql_fetch_array($query)) { $memberid = $smffetch["cpmsubtitle"]; $registerdate= $smffetch["title"]; $query_results=$query_results . '<br>' . $memberid . ': ' . $registerdate; } $query_results = $memberid . '<br>' . $registerdate; // builds the message body with the variables created.. $date = date("F j, Y"); $board = '5'; $msgSubject = $date; $msgBody = $query_results; $msgOptions = array( 'id' => 0, 'subject' => $msgSubject, 'body' => $msgBody, 'icon' => 'xx', ues 'smileys_enabled' => 1, 'attachments' => empty($attachIDs) ? array() : $attachIDs, ); $topicOptions = array( 'id' => empty($topic) ? 0 : $topic, 'board' => $board, 'poll' => null, 'lock_mode' => 0, 'sticky_mode' => null, 'mark_as_read' => true, ); $posterOptions = array( 'id' => $ID_MEMBER, 'name' => 'Mr. Roboto', 'email' => 'webmaster@yourdomain.com', 'update_post_count' => 1 ); $newTopic = empty($topic) || $topic == 0; createPost($msgOptions, $topicOptions, $posterOptions); if (isset($topicOptions['id'])) $topic = $topicOptions['id']; if ($newTopic) notifyMembersBoard(); else sendNotifications($topic, 'reply'); ?> Many thanks! Wookie

I want it to show the 40 or so records that is in the table where position is 0, at the moment it shows just the one, and as you correctly said...its the last one. I'm not entirely sure how to get all the reocrds and I wondered is using foreach was the way to go, but after reading for a few days now I have exhausted many ways of using this and am now at a bit of a loss as to where to go with it next. I had thought that assigning the id and date to variables that I was on the right track..but this code just produces one of the forty results I want. Any idea where I go from here? Regards Wookie

I've written the little snippet below that produces a members id and registerdate, what I want to be able to do is produce a list of these where the position = 0 (should be around 40 of them) If I understand things correctly from my reading this last two days I need to use foreach within this code snippet to get the job done, unfortunately all attempts have failed thus far, is it foreach I need to use and if so how do I apply it to the code I have already written? <?php $query = mysql_query("SELECT * FROM potdp WHERE position = '0'") or die("Error: ".mysql_error()); while($smffetch=mysql_fetch_array($query)) { $memberid = $smffetch["id"]; $registerdate= $smffetch["date"]; } $query_results = $memberid . '<br>' . $registerdate; ?> Many thanks in advance! Wookie

I'm trying to run this automated script that posts a new topic to a forum, using info gained via an sql query. The part of this script that actually posts at the forum is "test" from this line "$msgBody = 'test';" How do I add the following query so that the results are posted instead of "test"? Here's the query.. $result = mysql_query("SELECT * FROM smf_messages WHERE ID_BOARD='1'"); while($row = mysql_fetch_array($result)) { <?php include_once('/absolute path/SSI.php'); require_once($sourcedir . '/Post.php'); require_once($sourcedir . '/Subs-Post.php'); // comment out below line to run in shell if (!isset($context['user']) || !$context['user']['is_admin']) die ('Unknown error 744 has occurred'); $topic = 0; // set to 0 for new topic, set to a topic id to reply $board = 1; // set to valid board id $ID_MEMBER = 1; $_POST['subject'] = 'you\'re okay'; // the notify function uses this $msgSubject = addslashes($_POST['subject']); $msgBody = 'test'; $msgBody = prepBody( $msgBody); $msgOptions = array( 'id' => 0, // set id to modify 'subject' => $msgSubject, 'body' => $msgBody, 'icon' => 'xx', // look at smf_message_icons table to see all possible values 'smileys_enabled' => 1, 'attachments' => empty($attachIDs) ? array() : $attachIDs, ); $topicOptions = array( 'id' => empty($topic) ? 0 : $topic, 'board' => $board, 'poll' => null, 'lock_mode' => 0, 'sticky_mode' => null, 'mark_as_read' => true, ); $posterOptions = array( 'id' => $ID_MEMBER, 'name' => 'some name', 'email' => 'me@domain.com', 'update_post_count' => 1 ); $newTopic = empty($topic) || $topic == 0; createPost($msgOptions, $topicOptions, $posterOptions); if (isset($topicOptions['id'])) $topic = $topicOptions['id']; if ($newTopic) notifyMembersBoard(); else sendNotifications($topic, 'reply'); redirectexit('topic=' . $topic); // take an htmlized $txt and turn it into a smf compatible msg // no cr lf, no html tags, <br> turned into <br /> and <p> made into <br /><br /> function prepBody( $txt) { // cr lf to spaces $txt = str_replace( "\r", ' ', $txt); $txt = str_replace( "\n", ' ', $txt); // reduce blank spaces $txt = preg_replace( '/[ ][ ]+/', ' ', $txt); // turn <br> or <br /> into \n $txt = preg_replace( '!s*<br(s*/)?>\s*!i', "\n", $txt); // turn <p ...> and <p> into \n\n $txt = preg_replace( '/\s*<p([^>]+|)>\s*/i', "\n\n", $txt); // mercilessly strip all other html $txt = preg_replace( '/<[^>]+>/', '', $txt); $txt = preg_replace( '!</[a-z]+>!i', '', $txt); // turn \n back to <br /> $txt = str_replace( "\n", '<br />', trim( $txt)); return addslashes($txt); } ?> Many thanks in advance! Wookie

Many thanks rhodesa, much appreciated and topic is solved now. Wookie

This is an autoposting script that posts direct to SMF (Simple Machine Forum), I was given this to work with but I am getting the following error. Parse error: syntax error, unexpected '<' in /home/stev1965/public_html/foru/automate.php on line 125 I can see by previewing the message here the syntax is not quite right in the last part of this script due to the colours and the problem part being all black. I'm thinking that its the ?> in $txt = preg_replace( !s*<br(s*/)?>\s*!i', "\n", $txt); that is the error but could be wrong (and most probably...I am wrong). Can anyone see the problem to help me solve this? <?php include_once('/absolute path/SSI.php'); require_once($sourcedir . '/Post.php'); require_once($sourcedir . '/Subs-Post.php'); // comment out below line to run in shell if (!isset($context['user']) || !$context['user']['is_admin']) die ('Unknown error 744 has occurred'); $topic = 0; // set to 0 for new topic, set to a topic id to reply $board = 1; // set to valid board id $ID_MEMBER = 1; $_POST['subject'] = 'you\'re okay'; // the notify function uses this $msgSubject = addslashes($_POST['subject']); $msgBody = '[quote author=anon]<br> You can use bbc code here<p> This script assumes you have html-ized body which it will convert to smf compatible format [/quote] <p> it\'s all good '; $msgBody = prepBody( $msgBody); $msgOptions = array( 'id' => 0, // set id to modify 'subject' => $msgSubject, 'body' => $msgBody, 'icon' => 'xx', // look at smf_message_icons table to see all possible values 'smileys_enabled' => 1, 'attachments' => empty($attachIDs) ? array() : $attachIDs, ); $topicOptions = array( 'id' => empty($topic) ? 0 : $topic, 'board' => $board, 'poll' => null, 'lock_mode' => 0, 'sticky_mode' => null, 'mark_as_read' => true, ); $posterOptions = array( 'id' => $ID_MEMBER, 'name' => 'some name', 'email' => 'me@domain.com', 'update_post_count' => 1 ); $newTopic = empty($topic) || $topic == 0; createPost($msgOptions, $topicOptions, $posterOptions); if (isset($topicOptions['id'])) $topic = $topicOptions['id']; if ($newTopic) notifyMembersBoard(); else sendNotifications($topic, 'reply'); redirectexit('topic=' . $topic); // take an htmlized $txt and turn it into a smf compatible msg // no cr lf, no html tags, <br> turned into <br /> and <p> made into <br /><br /> function prepBody( $txt) { // cr lf to spaces $txt = str_replace( "\r", ' ', $txt); $txt = str_replace( "\n", ' ', $txt); // reduce blank spaces $txt = preg_replace( '/[ ][ ]+/', ' ', $txt); // turn <br> or <br /> into \n $txt = preg_replace( !s*<br(s*/)?>\s*!i', "\n", $txt); // turn <p ...> and <p> into \n\n $txt = preg_replace( '/\s*<p([^>]+|)>\s*/i', "\n\n", $txt); // mercilessly strip all other html $txt = preg_replace( '/<[^>]+>/', '', $txt); $txt = preg_replace( '!</[a-z]+>!i', '', $txt); // turn \n back to <br /> $txt = str_replace( "\n", '<br />', trim( $txt)); return addslashes($txt); } ?> Many thanks Wookie

I've spent all day with my good friend Google who has been unable to help me this time, so I pose a question here. I'm trying to copy one tables contents from one database to another (both databases are in the same location) and in the query I'm specify the FROM part as dbname.user.table. Would anyone be able to shed any light on why this is not working? error reporting is on, but when I run the php it just says "done" and reports no errors...and table "stevetest" remains empty. <?php error_reporting(E_ALL); require_once(dirname(__FILE__) . '/Settings.php'); $link = mysql_connect($db_server, $db_user, $db_passwd); mysql_select_db($db_name, $link); $sql = "INSERT INTO {$db_prefix}stevetest(`filepath`) SELECT * (`filepath`) FROM _copp1.photogra.cpg_pictures"; mysql_query($sql,$link); mysql_close($link); ?> Many thanks Steve...