Page 1 of 2
Help with mysql and php
Posted: Tue Aug 12, 2008 6:49 pm
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).
Re: Help with mysql and php
Posted: Tue Aug 12, 2008 7:07 pm
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
Re: Help with mysql and php
Posted: Wed Aug 13, 2008 6:14 am
by JKM
Thanks a bunch! <3
Re: Help with mysql and php
Posted: Wed Aug 13, 2008 7:43 am
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 />';
}
?>
Re: Help with mysql and php
Posted: Wed Aug 13, 2008 7:52 am
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?
Re: Help with mysql and php
Posted: Wed Aug 13, 2008 8:30 am
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 />';
}
?>
Re: Help with mysql and php
Posted: Wed Aug 13, 2008 8:37 am
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
Re: Help with mysql and php
Posted: Wed Aug 13, 2008 10:01 am
by JKM
Code: Select all
$query = "SELECT * FROM dvd ORDER BY id DESC";
I haven't got SSH access - only FTP.

Re: Help with mysql and php
Posted: Wed Aug 13, 2008 10:45 am
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.
Re: Help with mysql and php
Posted: Wed Aug 13, 2008 11:31 am
by JKM
The same error occured when I wrote
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');
Re: Help with mysql and php
Posted: Wed Aug 13, 2008 11:46 am
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.
Re: Help with mysql and php
Posted: Wed Aug 13, 2008 4:31 pm
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>?
Re: Help with mysql and php
Posted: Thu Aug 14, 2008 8:14 am
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.
Re: Help with mysql and php
Posted: Thu Aug 14, 2008 10:06 am
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!
Re: Help with mysql and php
Posted: Thu Aug 14, 2008 8:35 pm
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