SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do I retrieve row if the is not data in that rows column.

    What I want to do is have a script that selects the first row that doesn't have data in that column and then insert something in the columns of that row. Then move on to the next one.

    This is my database.

  2. #2
    SitePoint Member
    Join Date
    Jul 2006
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could update all of the empty ones with the same data using,
    PHP Code:
    mysql_query("UPDATE table SET uploads = '232', channelviews = '576', videoviews = '91231' WHERE uploads = ''"); 
    Or, fetch all the empty ones, and loop through them and update them individually.
    This way you can set the data for each row, instead of having them all the same.
    PHP Code:
    $fetch_empty mysql_query("SELECT id FROM table WHERE uploads = '' OR channelviews = ''");
    while(
    $empty mysql_fetch_array($fetch_empty))
    {    
        
    // Generate a random number here... use the variable in the query below?
        
    mysql_query("UPDATE table SET uploads = 'xx', channelviews = 'xx', videoviews = 'xx' WHERE id = '" $empty['id'] . "'");

    Cueburst - Support System
    FAQ Knowledge Base, Ticket Area and more.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "first" and "move on" are holdover concepts from record-at-a-time procedural thinking

    success in database applications comes from switching to set-at-a-time thinking

    when you run an sql statement, think of it as operating on all the rows of the table simultaneously

    now, what was it you wanted to do again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Adrian.jc View Post
    PHP Code:
    $fetch_empty mysql_query("SELECT id FROM table WHERE uploads = '' OR channelviews = ''");
    while(
    $empty mysql_fetch_array($fetch_empty))
    {    
        
    // Generate a random number here... use the variable in the query below?
        
    mysql_query("UPDATE table SET uploads = 'xx', channelviews = 'xx', videoviews = 'xx' WHERE id = '" $empty['id'] . "'");

    This is the one I'll try in the morning. (Gotta sleep)

    What I want to do is have another bit of code which looks up the values for these columns based on the username given and then moves onto the next row. But then if I run the php file later it will only update the new ones and not all of them.

    Also is there a way that I can get one row after another even if it has data in it?

    Thanks.

    I'll read your responses in the morning.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Techwiz101 View Post
    Also is there a way that I can get one row after another even if it has data in it?
    with a cursor, but seriously, read my previous post, this is ~not~ the direction you should be going
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    with a cursor, but seriously, read my previous post, this is ~not~ the direction you should be going
    Well what would you suggest to use, code wise?

    I think I might aswell go with what ever you're going to give me as I'm going to have to update it all and not just the empty ones.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Techwiz101 View Post
    Well what would you suggest to use, code wise?

    I think I might aswell go with what ever you're going to give me as I'm going to have to update it all and not just the empty ones.
    Please explain plain english (not php or mysql code) what you want to do.
    Which rows do you want to update, and where do the values come from that you want to put in the table?
    Last edited by guido2004; Nov 1, 2011 at 05:13. Reason: wrong quote

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Techwiz101 View Post
    I think I might aswell go with what ever you're going to give me as I'm going to have to update it all and not just the empty ones.
    please give an explanation of what columns you want to update, for which rows, and what values you want those columns to have
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to update all of the columns apart from the ID and username.

    I want the other columns to be set by, how do I explain it...
    Have a look at this thread I'm in the process of getting my way of retrieving the values for it.

    I need this to all go into a file called update.php which I want cron to run if I can.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Techwiz101 View Post
    Have a look at this thread I'm in the process of getting my way of retrieving the values for it.
    Let's suppose you have retrieved your values.
    Are they values for one specific row? For several rows? All rows?
    What is the connection between those values and the rows in the database table? How do you know which values must be inserted in which row in the database table?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Techwiz101 View Post
    I Have a look at this thread
    when you have something more concrete to offer in this thread, not that one, i'd be happy to help you with your UPDATE statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •