The Geeks & God Podcast

Selecting Multiple fields in MySQL through a PHP value

July 6, 2006 - 4:12pm

Joined: 11/28/2008

I am trying to select posts in MySQL from a phpBB forum.

The values I want to check are in a field called 'post_id'.
Eventualy, I want to get all posts that have a 'post_id' that matchs an array I will build.

CODE

$sql = 'SELECT * FROM `phpbb_posts_text` WHERE `post_id` = \'3\' OR \'1\' LIMIT 0, 200 ';

// Performing SQL query
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());

// Printing results in HTML
    while ($row = mysql_fetch_assoc($result)) {
        $post_subject = str_replace(' ', "-", $row["post_subject"]);
        echo '<a href="'. $phpbb_root_path. $post_subject. '-vt6.html">'.$row["post_subject"]. '</a>';
    }

You see, I made a custom page, and I am trying to turn the titles into links to the real forum topics. I have already been successful in printing out the links and them pointing to the forums. However, rather than JUST the posts that match the values '3' and '1' I also get post '2'. What is wrong with my SQL statement? And if anyone has experience in phpBB - am I doing it right? What if I have 200 topics is using the 'OR' command the right way to do it?

Any help would be appreciated /biggrin.gif" style="vertical-align:middle" emoid=":D" border="0" alt="biggrin.gif" />
God Bless.

Xeoncros - Code 2 Truth - Imrah - Code 2

July 6, 2006 - 5:03pm

Nexonen

Joined: 11/28/2008

You need to use WHERE post_id

You need to use WHERE post_id = '3' OR post_id = '1' - what you have done is like saying WHERE '1' which is actually always going to return true so you get all records returned.

You can also use

WHERE post_id IN ('1', '3')

~Andrew~

July 6, 2006 - 5:48pm

Xeoncross

Joined: 11/28/2008

Nexonen @ Jul 6 2006,

QUOTE(Nexonen @ Jul 6 2006, 03:03 PM)

You need to use WHERE post_id = '3' OR post_id = '1' - what you have done is like saying WHERE '1' which is actually always going to return true so you get all records returned.

You can also use
WHERE post_id IN ('1', '3')

So, if I had a lot of values like: 1, 7, 245, 12, and 15 in an array like this I could format them and put them in a string and run the SQL?

CODE

$post_ids = array('1', '7', '245', '12', '15');

foreach ($post_ids as $value) {
$fixed_post_ids = '\''. $value. '\', ';
}

$sql = 'SELECT * FROM `phpbb_posts_text` WHERE `post_id` IN ('. $fixed_post_ids. ') LIMIT 0, 200 ';

Is this the right way to do it?

Xeoncros - Code 2 Truth - Imrah - Code 2

July 6, 2006 - 6:08pm

Nexonen

Joined: 11/28/2008

Yeah you could do that but to

Yeah you could do that but to build up the string you need to concatenate it - your foreach loop will just overwrite the $fixed_post_ids each time, so you'd need to use .=

Change

QUOTE

$fixed_post_ids = '\''. $value. '\', ';

QUOTE

$fixed_post_ids .= '\''. $value. '\', ';

Alternatively you could use implode

CODE

$post_ids = array('1', '7', '245', '12', '15');
$fixed_post_ids = implode("', '", $post_ids);

$sql = 'SELECT * FROM `phpbb_posts_text` WHERE `post_id` IN ('. "'".$fixed_post_ids."'" .') LIMIT 0, 200 ';

~Andrew~

July 6, 2006 - 11:46pm

Xeoncross

Joined: 11/28/2008

Thanks for all of your help

Thanks for all of your help Nexonen! /biggrin.gif" style="vertical-align:middle" emoid=":D" border="0" alt="biggrin.gif" />

I finished (I think) the script. you can download it from:

http://learnphpfree.com/Unique Page - Show Forum Topics - Posts.zip

I am going to submit it to phpBB MODS now....

Xeoncros - Code 2 Truth - Imrah - Code 2