PHP and MySQL

gunnar

Hi,

I have a code for a discussion board working with Access that looks like this:

<?
$conn_id=odbc_connect("TEST","","");
$sql="SELECT * FROM forum WHERE testtable.Pid = 0 ORDER BY datum DESC ";
$result=odbc_exec($conn_id,$sql);
?>
<a href="new.php">
Nytt inlägg</a>

<table border="0" cellpadding="3">
<tr>
<th BGCOLOR="#CCCCCC">Ämne</th>
<th BGCOLOR="#99CCFF">Namn</th>
<th BGCOLOR="#CCCCCC">Epost</th>
<th BGCOLOR="#99CCFF">Datum</th>
<th BGCOLOR="#CCCCCC">Antal svar:</th>
<th BGCOLOR="#99CCFF">Senaste inlägg:</th>
</tr>
<?
while(odbc_fetch_row($result)){
?>
<tr>
<td BGCOLOR="#CCCCCC"><a href="bla.php?iid=<?=odbc_result($result,"iid")?>"><?=odbc_result($result,"Amne")?></a>
 
</td>
<td BGCOLOR="#99CCFF"><?=odbc_result($result,"Namn")?> 
</td>
<td BGCOLOR="#CCCCCC"><?=odbc_result($result,"Epost")?> 
</td>
<td BGCOLOR="#99CCFF"><?=odbc_result($result,"datum")?> 
</td>
<td BGCOLOR="#CCCCCC"><?=odbc_result($result,"Antal")?> 
</td>
<td BGCOLOR="#99CCFF"><?=odbc_result($result,"senaste")?> 
</td>
</tr>
<? }
?>
</table>

I have to tried convert it to work with MySQL by changing the odbc command to what I thought would fit MySQL like this:

<?
$lank=mysql_connect("localhost","","");
$sql="SELECT * FROM forum WHERE testtable.pid = 0 ORDER BY datum DESC ";
$result=mysql_db_query("tornsdb","$sql");
?>
<a href="testnew.php">
Nytt inlägg</a>

<table border="0" cellpadding="3">
<tr>
<th BGCOLOR="#CCCCCC">Ämne</th>
<th BGCOLOR="#99CCFF">Namn</th>
<th BGCOLOR="#CCCCCC">Epost</th>
<th BGCOLOR="#99CCFF">Datum</th>
<th BGCOLOR="#CCCCCC">Antal svar:</th>
<th BGCOLOR="#99CCFF">Senaste inlägg:</th>
</tr>
<?
while($rad=mysql_fetch_array($result)){
?>
<tr>
<td BGCOLOR="#CCCCCC"><a href="testbla.php?iid=<?=$rad["iid"]?>"><?=$rad["amne"]?></a>
 
</td>
<td BGCOLOR="#99CCFF"><?=$rad["namn"]?> 
</td>
<td BGCOLOR="#CCCCCC"><?=$rad["epost"]?> 
</td>
<td BGCOLOR="#99CCFF"><?=$rad["datum"]?> 
</td>
<td BGCOLOR="#CCCCCC"><?=$rad["antal"]?> 
</td>
<td BGCOLOR="#99CCFF"><?=$rad["senaste"]?> 
</td>
</tr>
<? }
?>
</table>

It works out just fine with Access but with MySQL the table is shown but nothing more so it must be my "translations" from odbc that miss something, I guess . Ányone who could tell me what?

RGDS

barand

I'd have serious reservations about his query

$sql="SELECT * FROM forum WHERE testtable.Pid = 0 ORDER BY datum DESC ";

You are selecting from table 'forum' but your where condition refers to a completely different table 'testtable' which is not included in the query.

gunnar

My mistake. The code working just fine with Access should read:

$sql="SELECT * FROM forum WHERE forum.Pid = 0 ORDER BY datum DESC ";

For MySQL the code should read:

$sql="SELECT * FROM forum WHERE forum.pid = 0 ORDER BY datum DESC ";
$result=mysql_db_query("tornsdb","$sql");

The database name is tornsdb and it has with one table, forum.

The problem seems to occur when I try to loop through the posts in $result with
while($rad=mysql_fetch_array($result)
to put them into the table with
<?=$rad["iid"]?>.

<?=odbc_result($result,"namn")?>works, <?=$rad["namn"]?> does not.

tsinka

Hi,

can you post some more code ? Do you get any error messages ?

barand

The odbc db functions don't have anything to return an associative array. My preferred method with odbc is

while (odbc_fetch_into($result, $array)) {
list($field1, $field2 ... $fieldn) = $array;

...

}

hth

gunnar

Originally posted by tsinka
Hi,

can you post some more code ? Do you get any error messages ?

Hi,

So this is the code I use together which Access which works:

<?
$conn_id=odbc_connect("Forum","","");
$sql="SELECT * FROM forum WHERE forum.Pid = 0 ORDER BY datum DESC ";
$result=odbc_exec($conn_id,$sql);
?>
<a href="nytt.php">
New message</a>

<table border="0" cellpadding="3">
<tr>
<th BGCOLOR="#CCCCCC">Topic</th>
<th BGCOLOR="#99CCFF">Name</th>
<th BGCOLOR="#CCCCCC">Email</th>
<th BGCOLOR="#99CCFF">Date</th>
<th BGCOLOR="#CCCCCC">Number of answers:</th>
<th BGCOLOR="#99CCFF">Latest message:</th>
</tr>
<?
while(odbc_fetch_row($result)){
?>
<tr>
<td BGCOLOR="#CCCCCC"><a href="las.php?iid=<?=odbc_result($result,"iid")?>"><?=odbc_result($result,"topic")?></a>
 
</td>
<td BGCOLOR="#99CCFF"><?=odbc_result($result,"Name")?> 
</td>
<td BGCOLOR="#CCCCCC"><?=odbc_result($result,"Email")?> 
</td>
<td BGCOLOR="#99CCFF"><?=odbc_result($result,"Date")?> 
</td>
<td BGCOLOR="#CCCCCC"><?=odbc_result($result,"Number")?> 
</td>
<td BGCOLOR="#99CCFF"><?=odbc_result($result,"latest")?> 
</td>
</tr>
<? }
?>
</table>

</body>
</html>

The posts generated by the sql question is collected in $result. Then I write a table with headlines Topic, Name etc etc. With "while etc " I loop the posts from $result into the fields in the table. Works just fine with Access

I put a lot of effort in this, since I am quite a newbie. Unfortunately my server provider does not support MS, only MySQL so I had to go on trying to "translate" the odbc commands and this is where I fail.

The attempt looks like this. (I have kept the Swedish words here - hope it is not too confusing - in the table on the php page and in the MySQL table. I have tried to write "sql" in $result both as sql and "sql" but this has not affected the malfunctioning of my code.

<?
$lank=mysql_connect("localhost","","");
$sql="SELECT * FROM forum WHERE forum.pid = 0 ORDER BY datum DESC ";
$result=mysql_db_query("tornsdb",""sql"");
?>

<a href="testnytt.php">
Nytt inlägg</a>
<table border="0" cellpadding="3">

<tr>
<th BGCOLOR="#CCCCCC">Ämne</th>
<th BGCOLOR="#99CCFF">Namn</th>
<th BGCOLOR="#CCCCCC">Epost</th>
<th BGCOLOR="#99CCFF">Datum</th>
<th BGCOLOR="#CCCCCC">Antal svar:</th>
<th BGCOLOR="#99CCFF">Senaste inlägg:</th>
</tr>

<?
while($rad=mysql_fetch_array($result)){
?>

</body>
</html>

/Gunnar

gunnar

Originally posted by tsinka
Hi,

can you post some more code ? Do you get any error messages ?

And, I do not get any error messages.

gunnar

Originally posted by tsinka
Hi,

can you post some more code ? Do you get any error messages ?

And, I do not get any error messages.

What happens with MySQL is that the table is drawn up with its headlines "Ämne", "Namn" etc but the loop "while .... fetch array" does not seem to provide the fields with any content.

tsinka

Hi,

to see if there is something going wrong when connecting to the database or executing the query you could change the query stuff to:

$id=mysql_connect("localhost","","") or die("couldn't connect to database"); 

mysql_select_db("tornsdb",$id) or die(mysql_error());

$sql="SELECT * FROM forum WHERE pid = 0 ORDER BY datum DESC"; 
$result=mysql_query($sql,$id); 

echo "Number of selected rows: ".mysql_num_rows($result)."<br>\n";

Are you sure that you can connect to the database with no user and no password ?

gunnar

Originally posted by tsinka
Hi,

to see if there is something going wrong when connecting to the database or executing the query you could change the query stuff to:
$id=mysql_connect("localhost","","") or die("couldn't connect to database"); 

mysql_select_db("tornsdb",$id) or die(mysql_error());

$sql="SELECT * FROM forum WHERE pid = 0 ORDER BY datum DESC"; 
$result=mysql_query($sql,$id); 

echo "Number of selected rows: ".mysql_num_rows($result)." \n";
Are you sure that you can connect to the database with no user and no password ? [/B]

I get "Number of selected rows=0".

I think I could connect using only "localhost","","". I have an insert.php

<?
$lank=mysql_connect("localhost","","");
$datum = date("Y-m-d H:i:s");

$sql="INSERT INTO forum(pid,amne,namn,epost,inlagg,datum) VALUES ($iid,'$amne','$namn','$epost','$inlagg','$datum')";
mysql_db_query("tornsdb","$sql");

$sql="UPDATE forum SET senaste='$datum',antal = antal+1 WHERE iid = $top";
mysql_db_query("tornsdb","$sql");
mysql_close ($lank);

echo "Ditt svar aer nu registrerat (Your answer is registered)";
?>
<a href="testforum.php">Tillbaka till alla inlägg (Back)</a>
</body>
</html>

page from where I connect like that and it is possible to write to the MySQL table using the code on insert.php.[

tsinka

Seems like there is no record in the table where pid = 0.

What do you get if you execute that query with e.g. the command line tool mysql or with phpMyAdmin ?

what do you get if you execute the query:

SELECT COUNT(*) FROM forum ?

e.g.

$id=mysql_connect("localhost","","") or die("couldn't connect to database"); 

mysql_select_db("tornsdb",$id) or die(mysql_error());

$sql="SELECT COUNT(*) AS cnt FROM forum"; 
$result=mysql_query($sql,$id);

$row = mysql_fetch_array($result);

echo "Number of rows: ".$row['cnt']."<br>\n";

or if you remove the where statement and do just a SELECT * FROM forum ?

tsinka

There might be an error in your INSERT query:

INSERT INTO forum(pid,amne,namn,epost,inlagg,datum)

a space might be missing between forum and (

INSERT INTO forum (pid,amne,namn,epost,inlagg,datum)

gunnar

Originally posted by tsinka
There might be an error in your INSERT query:

INSERT INTO forum(pid,amne,namn,epost,inlagg,datum)

a space might be missing between forum and (

INSERT INTO forum (pid,amne,namn,epost,inlagg,datum)

When I removed the where statement all records were shown. Cannot figure out why it worked so neatly with Access. The reason for Pid is to sort out the answers, where Pid is not 0, from the main topics, where Pid should be 0, on the forum main page. Now I get all the answers also shown in the table.

Maybe it has something to do with the "Default" value for Pid. It is not set to '0'.

forum(pid) or forum (pid) does not seem to alter anything.

However I'll try to work some with this to try to figure out where the problem is. If I get into trouble again I try to call for help!

RGDS🙂

tsinka

Hi,

it might be set to null per default. You might have to alter your table. Change the field to "not null" with a default of 0.

tsinka

with this queries:

ALTER TABLE forum MODIFY pid BIGINT NOT NULL, ALTER pid { SET DEFAULT '0' }

Change BIGINT to whatever type you set for pid

I can't test the query now but something like this should do it.