Jump to content

php if/else statement to process mysql results?


kpmedia

Recommended Posts

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 by kpmedia
Link to comment
Share on other sites

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 by kpmedia
Link to comment
Share on other sites

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
    }
 }
Link to comment
Share on other sites

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 by kpmedia
Link to comment
Share on other sites

 

 

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

 

.

Link to comment
Share on other sites

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 by kpmedia
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
} 
Link to comment
Share on other sites

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 by kpmedia
Link to comment
Share on other sites

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 by kpmedia
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.