Help with mysql and php

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Help with mysql and php

Post by JKM »

Hi there!

If someone could help me with this, I would be greatful! I'm making a database for my DVD's, and I've allready made a form that sends to the mysql db, but the thing I wan't to know, if how I should let each rows be posted? Now, my script only posts ONE of the rows. My script is pretty messy, so if anyone could give me a clean nice script for this, I would be greatful (as I've already mentioned).
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: Help with mysql and php

Post by lukewilkins »

JKM,

I believe this is what you are looking for ... just a simple way to read back all the rows of your database.

Code: Select all

 
$link = mysql_connect($dbHost,$dbUser,$dbPass) or die('Unable to connect to host.');
@mysql_select_db($dbName) or die('Unable to select database.');
 
First connect to your database, which I'm sure you have figured out by now.

Code: Select all

 
$query = "SELECT * FROM yourTableName";
$result = mysql_result($query,$link);
 
while($row = mysql_fetch_assoc($result)){
     foreach($row as $key=>$value){
          echo 'KEY(' . $key . ') VALUE(' . $value . ')';
     }
     echo '<br />';
}
 
After running your query, the while() function will move through each row which is stored as an array. The foreach() function flips through each of your columns of that row and stores the index as $key and the actual value as $value. I then echo a line break (<br />) at the end of each row.

Set that up in your script and I'm sure you'll see what I mean. Once you understand what is happening, you can edit the way it is being outputted and make it display in a table or however you'd like.

Hope that helps.

Luke
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Help with mysql and php

Post by JKM »

Thanks a bunch! <3
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Help with mysql and php

Post by JKM »

Hmm, I got this error after I tried your script:

Code: Select all

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in XX/db.php on line 13
 
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in XX/db.php on line 15
This is the whole code:

Code: Select all

<?
error_reporting(E_ALL);
$dbHost = 'localhost';
$dbUser = 'X';
$dbPass = 'XXX';
$dbName = 'XX';
 
 
$link = mysql_connect($dbHost,$dbUser,$dbPass) or die('Unable to connect to host.');
@mysql_select_db($dbName) or die('Unable to select database.');
 
$query = "SELECT * FROM XXXX";
$result = mysql_result($query,$link);
 
while($row = mysql_fetch_assoc($result)){
     foreach($row as $key=>$value){
          echo 'KEY(' . $key . ') VALUE(' . $value . ')';
     }
     echo '<br />';
}
?>
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: Help with mysql and php

Post by lukewilkins »

Make this change for me and let me know what it says:

Change

Code: Select all

$result = mysql_result($query,$link);
to

Code: Select all

$result = mysql_result($query,$link) or die(mysql_error());
echo 'QUERY(' . $query . ')';
echo 'RESULT(' . $result . ')';
Let me know the output from that. Are you sure your query is still running correctly?
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Help with mysql and php

Post by JKM »

Code: Select all

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in XX/dvd.php on line 13

Code: Select all

<?
error_reporting(E_ALL);
$dbHost = 'localhost';
$dbUser = 'X';
$dbPass = 'XXX';
$dbName = 'XX';
 
 
$link = mysql_connect($dbHost,$dbUser,$dbPass) or die('Unable to connect to host.');
@mysql_select_db($dbName) or die('Unable to select database.');
 
$query = "SELECT * FROM XXXX";
$result = mysql_result($query,$link) or die(mysql_error());
   echo 'QUERY(' . $query . ')';
   echo 'RESULT(' . $result . ')';
 
while($row = mysql_fetch_assoc($result)){
     foreach($row as $key=>$value){
          echo 'KEY(' . $key . ') VALUE(' . $value . ')';
     }
     echo '<br />';
}
?>
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: Help with mysql and php

Post by lukewilkins »

Yes, I believe you have an error in your query. Post your full query here and I'll take a look. Otherwise, run that exact query in mysql command line to see if it returns anything. If I can see your query I believe we'll find the solution.

Thanks,
Luke
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Help with mysql and php

Post by JKM »

Code: Select all

$query = "SELECT * FROM dvd ORDER BY id DESC";
I haven't got SSH access - only FTP. :-(
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: Help with mysql and php

Post by lukewilkins »

I really don't know what to tell you without knowing what you actually have in your database. That's a simple enough query that if you typing the table name and id field correctly (and they both exist) it should be working. Do you have phpMyAdmin? You could run the query there as well and see what errors it tells you about.

Or use $query = "SHOW FIELDS FROM dvd"; And post EVERYTHING that prints out on the screen here for me to see.
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Help with mysql and php

Post by JKM »

The same error occured when I wrote

Code: Select all

$query = "SHOW FIELDS FROM dvd";
But here's a dump from my mysql db:

Code: Select all

-- phpMyAdmin SQL Dump
-- version 2.11.6
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 13, 2008 at 11:25 AM
-- Server version: 4.1.22
-- PHP Version: 5.2.6
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
--
-- Database: `dvddb`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `dvd`
--
 
CREATE TABLE IF NOT EXISTS `dvd` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `imdb` varchar(20) character set utf8 collate utf8_danish_ci NOT NULL default '',
  `title` varchar(100) character set utf8 collate utf8_danish_ci NOT NULL default '',
  `pic` varchar(20) character set utf8 collate utf8_danish_ci NOT NULL default '',
  `director` varchar(100) character set utf8 collate utf8_danish_ci default NULL,
  `genre` varchar(50) character set utf8 collate utf8_danish_ci NOT NULL default '',
  `rating` varchar(5) character set utf8 collate utf8_danish_ci NOT NULL default '',
  `added` varchar(10) NOT NULL default '0',
  `review_yesno` char(3) NOT NULL default '',
  `review` varchar(50) character set utf8 collate utf8_danish_ci NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
 
--
-- Dumping data for table `dvd`
--
 
INSERT INTO `dvd` (`id`, `imdb`, `title`, `pic`, `director`, `genre`, `rating`, `added`, `review_yesno`, `review`) VALUES
(1, '123imdb', '123title', '123pic', '123director', '123genre', '123rating', '13.08-08', '0', '123review'),
(2, '456imdb', '456title', '456pic', '456director', '456genre', '456rating', '13.08-08', '0', '456review');
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: Help with mysql and php

Post by lukewilkins »

Shoot, so sorry dude.

Code: Select all

$result = mysql_result($query,$link) or die(mysql_error());
should be

Code: Select all

$result = mysql_query($query,$link) or die(mysql_error());
Stupid slip of the mind and definitely not what I was expecting to find as the problem. Here's a cleaner way to print your table instead of the first way I showed you.

Code: Select all

echo "<table>\n<tr>";
        for($i=0; $i < mysql_num_fields($result); $i++) {
            echo "<th>" . mysql_field_name($result, $i) . "</th>"; 
        }
        $table .= "</tr>\n";
        while($row = mysql_fetch_row($result)) {
            echo "<tr>\n";
            foreach($row as $value) {
                echo "<td>" . $value . "</td>";
            }
            echo "</tr>\n";
        }
        echo "</table>\n";
Sorry again for the hassle.
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Help with mysql and php

Post by JKM »

Hehe, np!

But how should I get f.i. imdb value to be posted like this: <a href="imdb.com\'.$imdb.'" target="_blank">IMDb</a>?
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: Help with mysql and php

Post by lukewilkins »

Like so:

Code: Select all

echo '<a href="http://imdb.com/' . $row['imdb'] . '" target="_blank">IMDb</a>';
That has to be inside the while() loop that I showed you. Otherwise it won't know what $row is.
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

Re: Help with mysql and php

Post by JKM »

Hmm, I didn't understand that "inside the while() loop", this:

Code: Select all

while($row = mysql_fetch_row($result)) {
    echo "<div>\n";
    echo '<a href="http://imdb.com/'.$row['imdb'].'" target="_blank">IMDb</a>';
    foreach($row as $value) {
        echo "<p>" . $value . "</p>\n";
    }
    echo "</div>\n";
}
lead to this:

Code: Select all

<b>Notice</b>:  Undefined index:  imdb in <b>XX/dvd.php</b> on line <b>25</b><br>
<a href="http://imdb.com/" target="_blank">IMDb</a><p>1</p>
<p>123imdb</p>
Edit: Thanks again for the superb help - I really appreciate it!
User avatar
lukewilkins
Forum Commoner
Posts: 55
Joined: Tue Aug 12, 2008 2:42 pm

Re: Help with mysql and php

Post by lukewilkins »

No problem about the help. It keeps me on my toes.

OK, first of all, you don't need to display PHP notice reporting. Put the following at the top of your page:

Code: Select all

error_reporting(E_ALL ^ E_NOTICE);
Now, to give you a little more control over how it is displayed, let's do this:

Code: Select all

while($row = mysql_fetch_row($result)) {
    echo "<div>\n";
    foreach($row as $key=>$value) {
        if($key=='imdb'){
                echo '<a href="http://imdb.com/'.$row['imdb'].'" target="_blank">IMDb</a>';
        }else{
                echo "<p>" . $value . "</p>\n";
        }
    }
    echo "</div>\n";
}
And just to make sure, do you still have the asterisk (*) in your query? If not and you have set to pull specific columns from the DB, make sure you include `imdb` in there. If the asterisk is there, you should be all set.

Let me know if you still have issues with it.
Luke
Post Reply