Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Regular Coder
    Join Date
    Aug 2006
    Posts
    311
    Thanks
    0
    Thanked 1 Time in 1 Post

    Mysql/php issues

    First off I'm not sure if this is a problem with php or mysql. I am asking it here because I am using php to pull the data.

    I have a script to insert data into the db
    PHP Code:
    $sql=("insert into experts_forum_post(`id`,`user_id`, `text`, `cat_id`, `forum_id`, `parent_id`,`date`,`time`,`heading`) values ('','{$_SESSION['member_id']}','{$_POST['question']}','{$_POST['category']}','{$_POST['forum']}','{$_POST['forum_sub']}','$date',NOW(),{$_POST['body']}')");

    mysql_query($sql) or die(mysql_error()); 
    This inserts fine and all the data is in the db but when I try and pull it using this query
    PHP Code:
    $sql mysql_query("select * from experts_forum_post where forum_id={$_GET['f']} order by date,time desc limit 0,10");
    while(
    $row mysql_fetch_array($sql)){ 
    alot of info here that is not relavent to this post

    Now here is the part that is baffling me. If I insert the data by the script it inserts fine but not appearing when I call the query but if I manually insert the exact same data(with the exception of the timestamp) the data in phpmyadmin and pull it comes up in query when I call it. If I play with the time stamp then some of the other rows that I had in the table do not come up. So I am really baffled with this. Is it possible that something is wrong in my sql queries? Or maybe something that I setup wrong in the database? I hope someone can help me out with this.

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Location
    Quebec
    Posts
    261
    Thanks
    6
    Thanked 7 Times in 7 Posts
    Try adding the following to your SQL query. There is no error checking so if there is indeed an error with query or database, you'll never know.

    PHP Code:
    $sql mysql_query("select * from experts_forum_post where forum_id={$_GET['f']} order by date,time desc limit 0,10") or die("Problem with select query: ".mysql_error()); 
    On a second note, I noticed you are limiting the query to 0 results? Why is that... no wonder nothing is showing up.

  • #3
    Regular Coder
    Join Date
    Aug 2006
    Posts
    311
    Thanks
    0
    Thanked 1 Time in 1 Post
    How am I limiting the rows to 0? I have set limit 0,10 which will return the first 10 rows. The query works fine when I manually enter in the data so I don't believe that the problem is with the query to display the data if anything it appears to be a problem with the insert query or the DB structure but I can't figure it out

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Location
    Quebec
    Posts
    261
    Thanks
    6
    Thanked 7 Times in 7 Posts
    try posting the code that goes in the while brackets.

  • #5
    Regular Coder
    Join Date
    Aug 2006
    Posts
    311
    Thanks
    0
    Thanked 1 Time in 1 Post
    PHP Code:
    $sql mysql_query("select * from experts_forum_post where forum_id={$_GET['f']} order by date,time desc limit 0,10") or die(mysql_error());
    echo 
    '<table width="505" border="0" CELLSPACING="0">';
    $i 0
    while(
    $row mysql_fetch_array($sql)){
    if ( 
    $i == )
                {
                    
    $bgcolor "#fffde8";
                }
                else 
                {
                    
    $bgcolor "#b1d0ff";
                }

    $photosmysql_query("select * from photos where member_id={$row['user_id']} and main_image=1") or die(mysql_error());
    $row1 mysql_fetch_array($photos) or die(mysql_error());
    $membersmysql_query("select * from members where member_id={$row['user_id']}") or die(mysql_error());
    $row2 mysql_fetch_array($members) or die(mysql_error());
    $forummysql_query("select * from experts_forums where forum_id={$row['forum_id']}") or die(mysql_error());
        
    $row3 mysql_fetch_array($forum) or die(mysql_error());
        
    echo 
    '
      <tr  bgcolor="'
    .$bgcolor.'">
        <th scope="col" width="100"><img src="http://www.labelfreeartists.com/'
    .$row1['photo_url'].'" width="40"><br></th>
        <th scope="col" text align="left"><div class="texttop"><a href="viewtopic.php?cat_id='
    .$row['cat_id'].'&f='.$row['forum_id'].'&id='.$row['id'].'">'.$row['heading'].'</a><div class="textsub">&nbsp;&nbsp;&nbsp;&nbsp;in&nbsp;'.$row3['forum_title'].'&nbsp;&nbsp;-&nbsp;Asked by&nbsp;'.$row2['member_name'].'&nbsp;-&nbsp;'.$row['date'];'</tr></div></div>';
    $i++;


  • #6
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Quote Originally Posted by Jacobb123 View Post
    The query works fine when I manually enter in the data so I don't believe that the problem is with the query to display the data if anything it appears to be a problem with the insert query
    I think you're right. Make sure your `id` field is set to index & auto_increment, and try this query:
    PHP Code:
    $sql"INSERT INTO `experts_forum_post` (`id`,`user_id`, `text`, `cat_id`, `forum_id`, `parent_id`,`date`,`time`,`heading`) VALUES(NULL,'{$_SESSION['member_id']}','{$_POST['question']}','{$_POST['category']}','{$_POST['forum']}','{$_POST['forum_sub']}','$date',NOW(),'{$_POST['body']}')"
    Also, you should always use mysql_real_escape_string() or similar on user input you insert into a query.

  • #7
    Regular Coder
    Join Date
    Aug 2006
    Posts
    311
    Thanks
    0
    Thanked 1 Time in 1 Post
    wow this is being a real bear. That last post did not do it. I'm going to post the db structure and maybe you all can make some sense and see if I missed something

    Code:
    CREATE TABLE IF NOT EXISTS `experts_forum_post` (
      `id` int(50) NOT NULL auto_increment,
      `user_id` int(50) NOT NULL,
      `text` text NOT NULL,
      `cat_id` int(50) NOT NULL,
      `forum_id` int(50) NOT NULL,
      `parent_id` int(5) NOT NULL,
      `time` time NOT NULL,
      `date` date NOT NULL,
      `heading` text NOT NULL,
      KEY `id` (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
    
    --

  • #8
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,039
    Thanks
    2
    Thanked 316 Times in 308 Posts
    The biggest thing that stands out is that your PHP query has single-quotes around INT values. I recommend removing them and seeing if data inserted by PHP can be retrieved by PHP.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #9
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Quote Originally Posted by Jacobb123 View Post
    wow this is being a real bear. That last post did not do it. I'm going to post the db structure and maybe you all can make some sense and see if I missed something
    Dump the data rows, at least one row inserted manually and one inserted by the script. In phpMyAdmin browse to the table, hit export, uncheck structure, and hit ok.

  • #10
    Regular Coder
    Join Date
    Aug 2006
    Posts
    311
    Thanks
    0
    Thanked 1 Time in 1 Post
    OK .....I have taken out the single quotes for INT values

    PHP Code:
    INSERT INTO `experts_forum_post` (`id`,`user_id`, `text`, `cat_id`, `forum_id`, `parent_id`,`date`,`time`,`heading`) VALUES(NULL,207,'This data was inserted by the php sscript',1,4,6,CURDATE(),NOW(),'This data was inserted by the php sscript'
    Here is the dump

    Code:
    INSERT INTO `experts_forum_post` (`id`, `user_id`, `text`, `cat_id`, `forum_id`, `parent_id`, `time`, `date`, `heading`) VALUES
    (1, 222, 'this row was entered manually', 1, 1, 0, '00:41:17', '2007-11-12', 'this row was entered manually'),
    (16, 207, 'This data was inserted by the php sscript', 1, 4, 6, '21:48:57', '2007-11-17', 'This data was inserted by the php sscript');

  • #11
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Those look fine; is it still causing problems? I don't see anything that would cause a problem with that code. The echo() from your first post has a syntax error near the end(";" should be "."), but that's it.

  • #12
    Regular Coder
    Join Date
    Aug 2006
    Posts
    311
    Thanks
    0
    Thanked 1 Time in 1 Post
    ok....I did a query by itself and it appers that the db info is ok. I ma looking at the way that I am pulling the data and I thought I had it setup right but it's giving me some problem so please check this out. I am trying to a mysql query inside a while loop and not sure if I have it setup properly. here it is

    PHP Code:
    <?
    echo '<table width="505" border="0" cellspacing="0">';
    $sql mysql_query("select * from experts_forum_post order by date desc limit 0,10");
    $i=0;
    while(
    $row mysql_fetch_array($sql)){
    $sql2mysql_query("select * from photos where member_id={$row['user_id']} AND main_image=1") or die(mysql_error());
    $row2 mysql_fetch_array($sql2) or die(mysql_error());

    if( 
    $i == )
                {
                    
    $bgcolor "#ffffff";
                }
                else 
                {
                    
    $bgcolor "#e8f3ff";
                }

    echo 
    '
      <tr bgcolor="'
    .$bgcolor.'" height="15">
      
        <th scope="col" width="100"><img src="http://www.labelfreeartists.com/'
    .$row2['photo_url'].'" width=40>';
        echo 
    '</th>
        <th scope="col" text align="left"><a href="viewtopic.php?cat_id='
    .$row['cat_id'].'&f='.$row['forum_id'].'&id='.$row['id'].'">'.$row['heading'].'</a>
        </tr><tr bgcolor="'
    .$bgcolor.'"><td></td><td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$row['date'];'</td></tr>';
        
    $i++;
    }

    echo 
    '</table>';


    ?>
    According to my poor logic this should work but if anyone could tell me a better way to do it that would be great

  • #13
    New Coder
    Join Date
    Apr 2007
    Posts
    89
    Thanks
    1
    Thanked 2 Times in 2 Posts
    PHP Code:
    $query 'SELECT * FROM my_table';
    $result mysql_query($query) or die('Query failed: ' mysql_error());
    while (
    $line mysql_fetch_array($resultMYSQL_ASSOC)) {
      

    pretty much cut and pasted from the manual, try this

  • #14
    Regular Coder
    Join Date
    Aug 2006
    Posts
    311
    Thanks
    0
    Thanked 1 Time in 1 Post
    the initial while loop works but as soon as I put in the second query one it goes a little wacky

  • #15
    Regular Coder
    Join Date
    Mar 2007
    Location
    Quebec
    Posts
    261
    Thanks
    6
    Thanked 7 Times in 7 Posts
    Perhaps its $sql2? I added in some quotes(' ') at main_image='1'

    PHP Code:
    $sql2mysql_query("select * from photos where member_id={$row['user_id']} AND main_image='1'") or die(mysql_error());
    $row2 mysql_fetch_array($sql2) or die(mysql_error()); 


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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