...

View Full Version : Mysql/php issues



Jacobb123
11-17-2007, 10:35 PM
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

$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

$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.

fl00d
11-17-2007, 11:34 PM
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.



$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.

Jacobb123
11-17-2007, 11:54 PM
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

fl00d
11-18-2007, 02:11 AM
try posting the code that goes in the while brackets.

Jacobb123
11-18-2007, 02:37 AM
$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 % 2 == 0 )
{
$bgcolor = "#fffde8";
}
else
{
$bgcolor = "#b1d0ff";
}

$photos= mysql_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());
$members= mysql_query("select * from members where member_id={$row['user_id']}") or die(mysql_error());
$row2 = mysql_fetch_array($members) or die(mysql_error());
$forum= mysql_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++;
}

Inigoesdr
11-18-2007, 02:59 AM
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:

$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() (http://php.net/mysql_real_escape_string) or similar on user input you insert into a query.

Jacobb123
11-18-2007, 03:59 AM
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


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 ;

--

CFMaBiSmAd
11-18-2007, 04:08 AM
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.

Inigoesdr
11-18-2007, 04:21 AM
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.

Jacobb123
11-18-2007, 04:53 AM
OK .....I have taken out the single quotes for INT values


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


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');

Inigoesdr
11-18-2007, 05:20 AM
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.

Jacobb123
11-18-2007, 06:17 AM
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


<?
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)){
$sql2= mysql_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 % 2 == 0 )
{
$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

psykx
11-18-2007, 01:54 PM
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

}

pretty much cut and pasted from the manual, try this

Jacobb123
11-18-2007, 03:45 PM
the initial while loop works but as soon as I put in the second query one it goes a little wacky

fl00d
11-18-2007, 04:19 PM
Perhaps its $sql2? I added in some quotes(' ') at main_image='1'


$sql2= mysql_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());

CFMaBiSmAd
11-18-2007, 04:43 PM
Define: "goes a little wacky" We only see the information that you post. If you can't or won't provide a complete description of what the result are or post them, no one can really help you.

Inigoesdr
11-18-2007, 06:43 PM
Also try echo()ing all of the queries and running them in phpMyAdmin to make sure the syntax is correct, and results are returned.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum