php if/else statement to process mysql results?

[kpmedia]

I'm having two issues:

 

(1) The correct mysql query for multiple rows

(2) The if/elseif/else to pull data from these rows, and process it based on the row data

 

I'm only pulling data where A=1 and B=1,2 -- so two possible entries there (call them B1 and B2).

I need a php if statement to choose whether the output of B is one of two urls (b1=google,b2=bing).

The actual script is far more complex, with more than just 1,2 from B. I've stripped all the excesses down to this one if/else issue and the db query. The output php doesn't matter here. And I can add more elseif once this problem is solved.

<?php 
mysql_connect(localhost, $db_username, $db_password);
 @mysql_select_db($db_database) or die("No connection");  

$query = "Select * FROM table WHERE column='stuff' AND parent='1,2' ORDER BY id DESC LIMIT 10";
 $query_result = mysql_query($query);
 $num_rows = mysql_num_rows($query_result);  
 mysql_close();  

?>  

<?php
    for($i=0; $i< $num_rows; $i++){   //start a loop 
    $stuff = mysql_result($query_result, $i, "column");

    $row = mysql_fetch_assoc($query_result, $i);
    if($row['parent'] == 1) {
        $url = 'http://google.com';
    } else {
        $url = 'http://www.bing.com';
    }

?>

My own first attempt at a if/then was 500.

I got help on another site to redo it (new code shown here), but the new if/else always show the else (Bing).

 

It was also at this time that I learned that "1,2" only showed 1.

 

Hoping that this site is far more friendly than StackOverflow.

 

.

Edited December 18, 2014 by kpmedia

[kpmedia]

I changed

	$row = mysql_fetch_assoc($query_result, $i);

to

	$row = mysql_fetch_assoc($query_result);

but the last row result still shows as the else (Bing), even when only a single row # is selected (ie, just 1, not 2).

Closer, but still not working.

 

.

Edited December 18, 2014 by kpmedia

[hansford]

I don't exactly understand the b1,2 thing, but whatever results you get should rely on the query.

I also don't understand the $i variable loop. Not understanding the problem I'm just throwing code out there.

//$query = "Select * FROM table WHERE column='stuff' AND parent='1,2' ORDER BY id DESC LIMIT 10";
 $query = "Select * FROM table WHERE column='stuff' AND(parent=1 OR parent=2) ORDER BY id DESC LIMIT 10";
 $query_result = mysql_query($query);
 $num_rows = mysql_num_rows($query_result);

 if ($num_rows > 0)
 {

 //mysql_close();

    //for($i=0; $i< $num_rows; $i++){   //start a loop
    //$stuff = mysql_result($query_result, $i, "column");
    $url = '';

    while($row = mysql_fetch_assoc($query_result))
    {
        if($row['parent'] == 1)
        {
            $url = 'http://google.com';
        }
        elseif($row['parent'] == 2)
        {
            $url = 'http://www.bing.com';
        }
        
        // if you get here and $url is == '';
        // then it isn't equal to either of those values
    }
 }

[kpmedia]

I tried

parent in (1,2)

which is the same as the OR

 

Problem is that the rows don't appear to line up with the data. It's off by 1.

 

Trying to explain this easily is somewhat difficult.

- the query is multiple columns -- WHERE, AND, AND, AND etc. For this example, I just showed 2: "stuff" and "parent"

- "parent" can pull several types of data: 1,2,3,4,5,6,7. For this example, I just wanted to make it easy, ie 1,2

- "stuff" is exacted

- there has to be a loop; it loops to the query limit (10)

 

The database does not contain full URLs for the data being pulled. So a php statement has to create the URL.

It's not actually Google/Bing, but more like;

- google.com/folder1/subfolder1

- google.com/folder2

- google.com/folder3/subfolder3/subsubfolder3

 

I'm not showing the entire script as written -- it's too long and confusing. Everything works fine, except this.

 

The loop is fine, the "stuff" var is fine.

Edited December 18, 2014 by kpmedia

[hansford]

 

 

here has to be a loop; it loops to the query limit (10)

 

That is what the query statement is for. You only wanted, at max, 10 results, so that is all the rows you will have in your result set, unless there are actually less than 10 rows for that given query.

What your code does is loop through the same result set, up to 10, and then you are grabbing different column values (from what you stated). This is highly inefficient. You should have one query or combined queries if needed, and then a single loop with all of your if/else statements to extract the data needed.

[QuickOldCar]

So are you trying to make a meta search, sitemaps, links index?

 

Maybe there is a better way to go about this than the way are attempting

Even if you pull 10 results in a sql limit, after you check them there could be 0-10 of them

 

Would be best to query with data you know is there like saving the domain in a column instead of the parent numbers and always determining which one they are in a loop.

 

When I want to save urls and know the domain...I parse the host and save as a new column upon saving the data.

It gets harder doing main hosts from subdomains, but you can easily do preg_match on them.

Can even go so far to say can do search or LIKE queries on the domain names or even the urls if had full ones saved.

[kpmedia]

No, it's not a search. That was just for illustration.

It displays the most recent results for a row. In this example, I'm just showing the problem data: a page and it's URL.

 

A query just queries data -- it doesn't process it. There's a loop -- like the WordPress loop.

 

The database is part of a CMS, and can't be altered. This isn't a "from scratch" project.

 

The main issue right now is that it pulls everything fine, but there's a -1 offset for the url. Everything else is fine -- but not the url. The query is probably fine, but the if/else statement has something wrong still.

 

 

.

[kpmedia]

Here: I further simplified it.
 

<?php
mysql_connect(localhost, $db_username, $db_password);
 @mysql_select_db($db_database) or die("No connection");  
$query = "Select * FROM table WHERE category in (1,2) ORDER BY id DESC LIMIT 10";
 $query_result = mysql_query($query);
 $num_rows = mysql_num_rows($query_result);  
 mysql_close();  
?>  

<?php
    for($i=0; $i< $num_rows; $i++){   //start a loop
    $row = mysql_fetch_assoc($query_result);

    if($row['category'] == '1') {
        $url = 'http://A/';
    } elseif ($row['category'] == '2') {
        $url = 'http://B/';
    }
?>

- I selected everything (*) which is where the page name and the url come from.
- When you run this code, the url (and ONLY the url) is offset by 1 from everything else that was queried and output.

The problem here is that the database does not display the full FQDN, only the page itself. Each category has a certain url, so it's easy enough to put back. But when you query multiple categories, you need to if/else to separate it all back out. And that works fine -- except for that offset. I have no idea where it comes from.

 

- page1 = page2 url
- page2 = page3 url
- page3 = ?

The $i loop controls everything, not just the url.

 

 

.

Edited December 18, 2014 by kpmedia

[QuickOldCar]

This doesn't look like a normal wordpress query.

 

This returns just 10 results from startrow 0 in mysql

$query = "Select * FROM table WHERE category in (1,2) ORDER BY id DESC LIMIT 10";

 

This tells it to start from row 0 and show 10

$query = "Select * FROM table WHERE category in (1,2) ORDER BY id DESC LIMIT 0,10";

 

This tells it to start at row 10 and just show 10

$query = "Select * FROM table WHERE category in (1,2) ORDER BY id DESC LIMIT 10,10";

 

So your query needs a dynamic value to control which row to start from, normal pagination.

$query = "Select * FROM table WHERE category in (1,2) ORDER BY id DESC LIMIT $startrow,10";

 

Sure you can do something like a +1 or -1 to current page but it sounds like wordpress has a pagination or it's using a pagination plugin.

pagination usually gets the page number and then some math on it to calculate which startrow needs to be at

 

For instance:

page 1 equates to startrow 0

page 2 equates to startrow 10

page 3 equates to startrow 20

[hansford]

Tell us this: Are we dealing with a single table and multiple columns or multiple tables with columns?

 

Just to let you know (not trying to be a smart-ass, just not understanding where you are coming from) - the following code piece creates an array called "$row" if any results are returned by the query.

Each array element will contain a new row of information.

$row = mysql_fetch_assoc($query_result);

If you wanted to loop through the result set you should be doing this:

while($row = mysql_fetch_assoc($query_result))
{

// do your if/else checks here

// this array will continue until all rows returned from your query are exhausted
} 

[hansford]

Is this a pagination problem. Just read QuickOldCar's post. "lights just came on" if it is lol.

[kpmedia]

No, I never have easy issues. It's was FOR WordPress, but not PART OF WordPress. WP is nice, but it has many limitations that can only be solved by exiting the loop and DIY in pure php. I often build stuff on top of WP, not in it.

 

It was solved here: http://www.webhostingtalk.com/showthread.php?t=1438963

 

I was trying to run mysql_result before mysql_fetch_assoc. That solved the if/else issue.

 

And I'd just never needed a non-simple mysql query that I can remember. (Sometimes it's hard to Google for something when you can't think of the exact jargon needed to do it! I'm too used to WP and vB, which use just basic 1,2,3,etc for multiples.)

 

Stick a fork in this one. But thanks for trying to help. This is definitely a friendlier site than SO.

 

.

Edited December 18, 2014 by kpmedia

[QuickOldCar]

Didn't hansford's example using the while for the loop help?

 

That's what you should do instead of a for loop anyway.

[kpmedia]

I'm not yet sure if his code will be helpful, but I will give it another look at some point. His suggestion to entirely remove the for loop is something I want to look into. The actual code is too complex right now and would break by simply removing it. I wanted to get it working first, then I can go back and look at ways to refine it. For example, replacing the deprecated code. This is one of those things I've had in my code library for several years now, having both taken snippets from other scripts online, as well as writing parts of it from scratch.

Edited December 18, 2014 by kpmedia