Help with mysql and php
Moderator: General Moderators
Help with mysql and php
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).
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).
- lukewilkins
- Forum Commoner
- Posts: 55
- Joined: Tue Aug 12, 2008 2:42 pm
Re: Help with mysql and php
JKM,
I believe this is what you are looking for ... just a simple way to read back all the rows of your database.
First connect to your database, which I'm sure you have figured out by now.
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
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.');
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 />';
}
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
Thanks a bunch! <3
Re: Help with mysql and php
Hmm, I got this error after I tried your script:This is the whole code:
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 15Code: 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 />';
}
?>- lukewilkins
- Forum Commoner
- Posts: 55
- Joined: Tue Aug 12, 2008 2:42 pm
Re: Help with mysql and php
Make this change for me and let me know what it says:
Change
to
Let me know the output from that. Are you sure your query is still running correctly?
Change
Code: Select all
$result = mysql_result($query,$link);Code: Select all
$result = mysql_result($query,$link) or die(mysql_error());
echo 'QUERY(' . $query . ')';
echo 'RESULT(' . $result . ')';Re: Help with mysql and php
Code: Select all
Warning: mysql_result(): supplied argument is not a valid MySQL result resource in XX/dvd.php on line 13Code: 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 />';
}
?>- lukewilkins
- Forum Commoner
- Posts: 55
- Joined: Tue Aug 12, 2008 2:42 pm
Re: Help with mysql and php
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
Thanks,
Luke
Re: Help with mysql and php
Code: Select all
$query = "SELECT * FROM dvd ORDER BY id DESC";- lukewilkins
- Forum Commoner
- Posts: 55
- Joined: Tue Aug 12, 2008 2:42 pm
Re: Help with mysql and php
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.
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
The same error occured when I wroteBut here's a dump from my mysql db:
Code: Select all
$query = "SHOW FIELDS FROM dvd";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');- lukewilkins
- Forum Commoner
- Posts: 55
- Joined: Tue Aug 12, 2008 2:42 pm
Re: Help with mysql and php
Shoot, so sorry dude.
should be
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.
Sorry again for the hassle.
Code: Select all
$result = mysql_result($query,$link) or die(mysql_error());Code: Select all
$result = mysql_query($query,$link) or die(mysql_error());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";Re: Help with mysql and php
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>?
But how should I get f.i. imdb value to be posted like this: <a href="imdb.com\'.$imdb.'" target="_blank">IMDb</a>?
- lukewilkins
- Forum Commoner
- Posts: 55
- Joined: Tue Aug 12, 2008 2:42 pm
Re: Help with mysql and php
Like so:
That has to be inside the while() loop that I showed you. Otherwise it won't know what $row is.
Code: Select all
echo '<a href="http://imdb.com/' . $row['imdb'] . '" target="_blank">IMDb</a>';Re: Help with mysql and php
Hmm, I didn't understand that "inside the while() loop", this:lead to this:
Edit: Thanks again for the superb help - I really appreciate it!
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";
}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>- lukewilkins
- Forum Commoner
- Posts: 55
- Joined: Tue Aug 12, 2008 2:42 pm
Re: Help with mysql and php
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:
Now, to give you a little more control over how it is displayed, let's do this:
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
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);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";
}Let me know if you still have issues with it.
Luke