1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Extract from database I need a little help

Discussion in 'PHP' started by floriano, Sep 2, 2015.

  1. #1
    I need a help for this code php, I want to extract from database...
    my comment is in code.
    My code extract only from ad1 ...

    <?php
            /* I have two table: `userlogin_tbl` table and `ads` table...
               I want to SELECT `adscode_big` from `userlogin_tbl` WHERE `channel_name`='$channel_name' 
               if column `adscode_big` is empty
               need add value from table `ads` column ad1                                                      */
    
            $channel_name = $_GET['v'];
            $var1 = "";
            $query = mysql_query("SELECT * FROM `userlogin_tbl` WHERE `channel_name`='$channel_name'");
    
            while ($row = mysql_fetch_array($query)) {
                 // this column is on table userlogin_tbl
                $adscode_big = $row["adscode_big"];
    
                if ($row["adscode_big"] == '') {
                    $sql = mysql_query("SELECT * FROM `ads`");
    
                    while ($row1 = mysql_fetch_array($sql)) {
                         // this column is on table ads
                        $adscode_big = $row1['ad1'];
    
                        echo $adscode_big;
                    }
                } else {
                    $adscode_big = $row["ad1"];
    
                    echo $adscode_big;
                }
            }
            ?>
    
    PHP:
    Thanks in advance for any help !
     
    Last edited: Sep 2, 2015
    floriano, Sep 2, 2015 IP
  2. CristianG.

    CristianG. Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #2
    Fist you need fo use the query correctly because in some php the quest will not execute.

    
    $query = mysql_query("SELECT * FROM `userlogin_tbl` WHERE `channel_name`='{$channel_name}';");
    
    Code (markup):
    Every time when you try to select please you num_rows to check if the query have rows !

    
    if(mysql_num_rows($query) > 0){
          // Put the job here
    }
    
    Code (markup):
    Next check if empty or null the $row

    
    if (empty($row["adscode_big"]) || !$row["adscode_big"]) {
    
    }
    
    Code (markup):
    Your code !

    
    <?php
       /* I have two table: `userlogin_tbl` table and `ads` table...
         I want to SELECT `adscode_big` from `userlogin_tbl` WHERE `channel_name`='$channel_name'
         if column `adscode_big` is empty
         need add value from table `ads` column ad1  */
    
       $channel_name = $_GET['v'];
       $var1 = "";
       $query = mysql_query("SELECT * FROM `userlogin_tbl` WHERE `channel_name`='{$channel_name}';");
       if(mysql_num_rows($query) > 0) {
         while ($row = mysql_fetch_array($query)) {
           // this column is on table userlogin_tbl
           //$adscode_big = $row["adscode_big"]; YOU DO NOT NEED TO SET NEW VARIABLE !!!
    
           if (empty($row["adscode_big"]) && !$row["adscode_big"]) {
             $sql = mysql_query("SELECT * FROM `ads`;");
    
             while ($row1 = mysql_fetch_array($sql)) {
               // this column is on table ads
               $adscode_big = $row1['ad1'];
    
               echo $adscode_big;
             }
           } else {
             $adscode_big = $row["ad1"];
    
             echo $adscode_big;
           }
         }
       }
    ?>
    
    Code (markup):
    The main issues is, you need to explain more because in "need add value from table `ads` column ad1 " I do not understand

    To add new value you need to use UPDATE or INSERT but I do not have any clue what value you need !


    Next question is :

    The channel_name is unique or you have multiple whit same name`s.

    I will send you the contact e-mail to help you if you wish !

    Regards

    EDIT : Fixed you do not need to while (loop) the query when the name is unique! We sort out on Skype!

    
    <?php
       /* I have two table: `userlogin_tbl` table and `ads` table...
         I want to SELECT `adscode_big` from `userlogin_tbl` WHERE `channel_name`='$channel_name'
         if column `adscode_big` is empty
         need add value from table `ads` column ad1  */
     
       $channel_name = $_GET['v'];
       $var1 = "";
       $query = mysql_query("SELECT * FROM `userlogin_tbl` WHERE `channel_name`='{$channel_name}' LIMIT 1;");
       if(mysql_num_rows($query) > 0) {
         $row = mysql_fetch_array($query);
         if (empty($row["adscode_big"]) && !$row["adscode_big"]) {
           $sql = mysql_query("SELECT * FROM `ads`;");
           if(mysql_num_rows($sql) > 0) {
             $row1 = mysql_fetch_array($sql);
             $adscode_big = $row1['ad1'];
       
             echo $adscode_big;
           }
         } else {
           $adscode_big = $row["adscode_big"];
       
           echo $adscode_big;
         }
       } else {
         echo "Canalul : {$_GET['v']}, nu exista !";
       }
    
    Code (markup):
     
    Last edited: Sep 2, 2015
    CristianG., Sep 2, 2015 IP
  3. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #3
    Also, this is 2015 not 2005, you shouldn't be using the deprecated and soon to no longer even exist in PHP "mysql_" functions -- NOR if you care about security should you be blindly dumping $_GET values (or any values for that matter) into a query string. The method used to fetch or not fetch the first result row is kind-of rubbish too.

    I would be rejecting empty results at the query level in the outer query, something like:

    	SELECT adscode_big
    	FROM userlogin_tbl
    	WHERE channel_name = ?
    		AND adscode_big IS NOT NULL 
    		AND adscode_big != ``
    Code (markup):
    that way only valid result sets will happen. Then you can just do a if (result) do {} else {}. Likewise as you are only fetching and using one column, ONLY FETCH THAT ONE COLUMN!!! PDO::fetchColumn() would then speed things up further.

    Using a MODERN connection technique like PDO, that would go something like this:

    /*
    	ASSUMES:
    		variable $db is a connected PDO object
    		php 5.4/newer arrays are available
    */
    
    $stmt = $db->prepare('
    	SELECT adscode_big
    	FROM userlogin_tbl
    	WHERE channel_name = ?
    		AND adscode_big IS NOT NULL 
    		AND adscode_big != ``
    ');
    $stmt->execute( [ $_GET['v'] ] );
    if ($ad = $stmt->fetchColumn()) {
    	do { echo $ad; } while ($ad = $stmt->fetchColumn());
    } else {
    	$stmt = $db->exec('SELECT ad1 FROM ads');
    	while ($ad = $stmt->fetchColumn()) echo $ad;
    }
    Code (markup):
    Guessing slightly, but that should check if there are no results OR all the results are empty on the first query. If not, output the results, if so, output all ads from the ads.ad1

    Mind you untested code, may be a typo or two.

    Really, blindly dumping variables into query strings and using global scope database connections is outdated nonsense that was not secure, there's a REASON for the GIANT RED WARNING BOXES in the manual on that. That's something even more important given that as of PHP 7, those will NO LONGER EVEN EXIST.
     
    deathshadow, Sep 2, 2015 IP
    Arick unirow and ryan_uk like this.
  4. lasersgopew

    lasersgopew Member

    Messages:
    15
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    48
    #4
    
    $channel_name = $_GET['v'];
    $var1 = "";
    $query = mysql_query("SELECT * FROM `userlogin_tbl` WHERE `channel_name`='$channel_name'");
    
    PHP:
    Never, ever do this. Not only is mysql_query() deprecated, but running queries with unsanitized inputs like that will get you owned. All someone has to do is visit
    http://yoursite.com/thatfile.php?v=';DROP TABLE userlogin_tbl
    Code (markup):
    and your "userlogin_tbl" is gone. They could literally do almost anything to your database like that.

    http://www.phptherightway.com/#databases
     
    lasersgopew, Sep 2, 2015 IP
    Arick unirow, ryan_uk and deathshadow like this.
  5. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #5
    Which is WHY the old mysql_ is deprecated, soon to no longer even EXIST, and WHY if you have data to be plugged into a query you're supposed to use mysqli:: prepare or PDO:: prepare with mysqli:: bindParam, PDOStatement:: bindParam or passing an array to PDOStatement:: execute.

    Sending the data in the query has always been halfwit nonsense just BEGGING to get PWNED. Honestly I think they should make the ENGINES start rejecting the idea wholesale so that all values HAVE to be passed separate.

    Hell, still pisses me off that emulated prepares is still the blasted default behavior -- how malfing stupid is that?

    If I could hit "like" on that post more than once, I'd do so endlessly.
     
    deathshadow, Sep 2, 2015 IP
    DomainerHelper likes this.
  6. lasersgopew

    lasersgopew Member

    Messages:
    15
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    48
    #6
    I'm glad to see it go, though it'll be a shame to lose my favorite password, "' OR 1=1;--". Oh well, c'est la vie.

    I think as long as people are using SQL directly, plenty of "developers" will find a way to make their code vulnerable. Take for example,
    
    $table = $_GET['table'];
    $username = $_GET['username'];
    
    $stmt = $pdo->prepare("SELECT ID FROM {$table} WHERE username = :user");
    $stmt->bindParam(':user', $username, PDO::PARAM_STR);
    $stmt->execute();
    
    PHP:
    Wouldn't surprise me one bit to see that in production, however...
    
    file.php?table=test_table;DROP TABLE test_table;--&username=doesntmatter
    
    Code (markup):
    and once again, the table is gone. But hey, at least the parameters were safe.
     
    Last edited: Sep 3, 2015
    lasersgopew, Sep 3, 2015 IP
  7. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #7
    @lasergopew -- Anyone DUMB ENOUGH to generate the table name from user input DESERVES to have the table gone.

    ... and to do that they STILL are plugging user input into the querystring which is STILL herpafreakingderp development.
     
    deathshadow, Sep 3, 2015 IP
  8. lasersgopew

    lasersgopew Member

    Messages:
    15
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    48
    #8
    At least they're still a step above the people that pass user input directly into unescaped shell commands; I've seen that a few times...
     
    lasersgopew, Sep 3, 2015 IP
  9. floriano

    floriano Well-Known Member

    Messages:
    74
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    113
    #9
    I need configure my server for technique PDO ?

    Can anyone write me correct code technique with PDO or without PDO technique, please.

    PDO code technique please tell me and what should I do to make this code work.

    Thank you in advance!
     
    floriano, Sep 3, 2015 IP
  10. CristianG.

    CristianG. Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #10
    You need to change all the script in PDO to work ! Because you have the main configuration and all your website in mysql_ you need to change all in PDO or mysqli :)
     
    CristianG., Sep 3, 2015 IP
  11. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #11
    No, he doesn't. He SHOULD, but there is no reason you can't have multiple connections / interfaces / DB-connectors present. You can run the whole site on mysql_ and just modify this one single query to use PDO (why you would do anything that stupid is a whole different matter).
     
    PoPSiCLe, Sep 4, 2015 IP
  12. CristianG.

    CristianG. Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #12
    The issues was fixed, the main issues was on him basically the code was rubbish to many errors, and the explication was to low.

    In second of my post I told him about the getting query, basically when you take a query by selection you need to think in 2 ways.

    1. When you select more that 1 selection you will use " while "
    2. When you select only 1 you do not need to while(loop) the query.

    The second of the matter was the condition, every time when you pass a selection or update etc , you need to check if is affected by row or num by row, so every time you need pass this:
    
    if(query_num_rows($query) > 0) for while etc,
    if(query_num_rows($query) == 1) of only one selection
    
    Code (markup):
    To build something you must understand and think about the code ! Most of developer take the code and just edit it.

    Most of people was right about changing the mysql_ because is deprecated. but the main is not this solution the solution was to help or send the expectation about this mistake !

    Regads
     
    CristianG., Sep 4, 2015 IP
  13. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #13
    ... and if you code properly, or KNOW what you can expect of the result, you don't have to check for "num rows" (granted, they're sometimes good to have, for instance to check if a result even exist), but most modern db-connectors will do this for you without having to run a row-count.
     
    PoPSiCLe, Sep 4, 2015 IP
  14. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #14
    No offense @CristianG, but have you been taking English lessons from Oswald Bates?

    Really there are few cases where while vs. no while is something to test for; if the result is false it won't loop, if it's one result it loops one, if it's multiple results it loops multiple times... the code overhead of pulling the row count TWICE and running if statements would be the same if not higher than just doing a while. As such there are damned few times where I'd even consider using IF on that.

    ... and even if I did, that's where if/do/while comes into play. A typical scenario for that would be for example if outputting a list, but you don't want a list and an error message instead if 0 rows.

    if ($row = $stmt->fetch()) {
      echo '<h2>Search Results:</h2><ul>';
      do {
       echo '<li>', $row['text'], '</li>';
      while ($row = $stmt->fetch());
      echo '</ul>';
    } else echo '<p class="error">No Results Found</p>';
    Code (markup):
    For example.
     
    deathshadow, Sep 5, 2015 IP
  15. CristianG.

    CristianG. Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #15
    Like I say, the fact in the matter is only the topic not entirety functionality.
    @deathshadow you besides our conversation

    Fist when you select by id with limit 1 you never use while because you do not need, take a look for my example example :

    
    // SELECT QUERY
    $query = mysql_query("SELECT * FROM `customer` WHERE `id`='1' LIMIT 1");
    // CHECK IF EXIST IN DATABASE;
    if(mysql_num_rows($query) == 1) {
       // set $row for all tables
       $row = mysql_fetch_array($query);
       echo $row['name']; // etc
    } else {
       echo "No Results Found";
    }
    
    Code (markup):


    Where you see the error from 0 ? in this case only if query have rows 1 will proceed our functionality but if not will give the "No Results Found"

    Like I say in first words , you besides our conversation

    So if you have issues on getting the messages no worry stop posting !!


    @ PoPSiCLe every time when you check the query first you will check the num_rows because you do not use PDO ! on mysql the safety way to check is num_rows. I found a lot of issues when if the query was check by other thinks :p


    Regards
     
    Last edited: Sep 5, 2015
    CristianG., Sep 5, 2015 IP
  16. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #16
    That's STILL a pointless use of fetching the count. (and still a nasty case of Engrish moist goodry / Yodaspeak. Are you using google translate or something?)

    If there's one result, sure, you don't use while, you don't use a result count EITHER. EVEN using the outdated mysql_ functions that have zero business in anything written after ~2007.

    $query = mysql_query("SELECT * FROM customer WHERE id=1 LIMIT 1");
    // CHECK IF EXIST IN DATABASE;
    if ($row = mysql_fetch_array($query)) {
       echo $row['name'];
    } else {
       echo 'No Results Found';
    }
    Code (markup):
    See, you have a POINTLESS use of mysql_num_rows EVEN without using mysqli or PDO. If "while" can use a fetch, so can an "if". They both take boolean results as their condition!

    Very, VERY rarely do you ever need to go for the number of rows in a result set; usually if you do it's for things like pagination, and for that one usually pulls a count(*) before pulling a limited result set. (sadly it's usually faster to run two queries than to return a full result set when only using part of it).

    Which is just PART of why your code from your first post in the thread is just as bad as the OP's. IF one were to retain the outdated mysql_ functions and to hell with if it's just going to give up the ghost once 5.x exits support:

    <?php
    
    $qChannel = mysql_query('
    	SELECT *
    	FROM userlogin_tbl
    	WHERE channel_name=`' . mysql_real_escape_string($_GET['v']) . '`
    	LIMIT 1
    ');
    
    if ($user = mysql_fetch_assoc($qChannel) {
    
    	if (empty($row['adscode_big']) || !$row['adscode_big'])) {
    		$qAdverts = mysql_query('SELECT ad1 FROM ads LIMIT 1');
    		if ($advert = mysql_fetch_assoc($qAdverts)) echo $advert['ad1'];
    	} else echo $user['adscode_big'];
    	
    } else echo 'Canalul : ', htmlspecialchars($_GET['v']), ', nu exista!';
    Code (markup):
    SANITIZE the input for the query, SANITIZE the input for output when the result is empty, there is NO reason to check the row count since 0 rows is false, return the row as an associative array since you aren't using the numeric indexes, fix the logic so that a non-empty false is trapped, get some slightly more meaningful variables in there, delete all the "variables for nothing" and brackets for nothing, stop using double quotes since they parse slower, format the larger query for better legibility, reduce the query set to just the row being used (I left user alone on the assumption other data is being used later), yada, yada, yada... Of course all this assumes an isset($_GET['v']) is done ahead of this code.

    No row counts needed. Wasted step, wasted processing time.
     
    Last edited: Sep 5, 2015
    deathshadow, Sep 5, 2015 IP
  17. CristianG.

    CristianG. Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #17
    Omg sorry mate :)) I`m sorry I never see you are a "smart" American :)) off you are the GOD IN EARTH :))

    :-j

    Honestly you are so fuk... annoying because in the script all _POST _GET set
    htmlspecialchars in first place, here is only a example, like I say :)) I HATE People like you who try to show off ! :))

    Back off mate stop be a "smart"
     
    Last edited: Sep 5, 2015
    CristianG., Sep 5, 2015 IP
  18. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #18
    @ChristianG: really, learn to speak English. I can see you claim to be from Leeds, but seriously, I've never, ever even heard a loiner speak this much gibberish.
    I'm not entirely sure what you were trying to tell me, but if you were trying to tell me that using mysql_num_rows is the right/smart way to do thing because you're using mysql_ instead of mysqli_ or PDO. then you're dumber than I thought. You don't use mysql_! Plain and simple. EVER.
    And as @deathshadow now repeatedly have pointed out, there's no reason to run a count to check to see if you only got one result, if you've already limited the result in the query itself. Either it's there, or the result is non-existent / empty. No need to run a count.
    I took a look at the codebase of a webapp I'm currently writing, I guess there is about 50k lines of code, or something by now - I use count() in a query one or two places, and both of those are for actually checking to see if there is duplicate content, before cleaning that up. It's almost never run in "real life" scenarios, it's just to have a fall-back, in case something goes wrong somewhere else.
     
    PoPSiCLe, Sep 7, 2015 IP
    deathshadow likes this.
  19. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #19
    +1+1+1+1+1... in a serious nasty case of needing to get medieval Samuel L. Jackson style: "Englisc, mōdor wyrter! Gedōn ēow cweþan hit!" kind of way.

    What does Marcellus Wallace look like?
     
    deathshadow, Sep 7, 2015 IP
    PoPSiCLe likes this.
  20. DomainerHelper

    DomainerHelper Well-Known Member

    Messages:
    445
    Likes Received:
    20
    Best Answers:
    0
    Trophy Points:
    100
    #20
    Deathshadow gave a perfect response and should be thanked for taking so much time for such a thorough response. I would not have been so generous with my time for free. You need to get off the Internet and return to grammar school to learn some manners. I sense much butthurt in you, for no valid reason.
     
    DomainerHelper, Sep 9, 2015 IP
    Arick unirow likes this.