Help with sql / 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

Post Reply
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Help with sql / php

Post by spamyboy »

Now I finnaly understood how should look database structure for that kind of think.
Basicly it's movies database, where in the mane table I keep movie title and some other misc stuff.
What I don't understand is how to list a movies and display theyr multy genres/actors and so on not making hundret queries...
If anyone will try to help me on this, pleas give me an example on also how to edit/add/delete them.
Thank you and here is what I theoricly created over phpMyAdmin, this should be the structure how it should look.

Code: Select all

-- phpMyAdmin SQL Dump
-- version 2.11.0
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 04, 2007 at 09:32 PM
-- Server version: 5.0.45
-- PHP Version: 5.2.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `movies`
--

-- --------------------------------------------------------

--
-- Table structure for table `actors`
--

CREATE TABLE `actors` (
  `actor_id` tinyint(11) NOT NULL auto_increment,
  `actor_fullname` varchar(255) NOT NULL,
  `descriotion` text NOT NULL,
  PRIMARY KEY  (`actor_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `actors`
--

INSERT INTO `actors` (`actor_id`, `actor_fullname`, `descriotion`) VALUES
(1, 'James Bond', 'Agent 007');

-- --------------------------------------------------------

--
-- Table structure for table `actors_relations`
--

CREATE TABLE `actors_relations` (
  `movie_id` tinyint(11) NOT NULL,
  `actor_id` tinyint(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `actors_relations`
--

INSERT INTO `actors_relations` (`movie_id`, `actor_id`) VALUES
(1, 1);

-- --------------------------------------------------------

--
-- Table structure for table `directors`
--

CREATE TABLE `directors` (
  `director_id` tinyint(11) NOT NULL auto_increment,
  `director_fullname` varchar(255) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`director_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `directors`
--

INSERT INTO `directors` (`director_id`, `director_fullname`, `description`) VALUES
(1, 'Tom Thomson', 'An good movies director...');

-- --------------------------------------------------------

--
-- Table structure for table `directors_relations`
--

CREATE TABLE `directors_relations` (
  `movie_id` tinyint(11) NOT NULL,
  `director_id` tinyint(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `directors_relations`
--

INSERT INTO `directors_relations` (`movie_id`, `director_id`) VALUES
(1, 1);

-- --------------------------------------------------------

--
-- Table structure for table `genres`
--

CREATE TABLE `genres` (
  `genre_id` tinyint(11) NOT NULL auto_increment,
  `genres` varchar(255) NOT NULL,
  PRIMARY KEY  (`genre_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `genres`
--

INSERT INTO `genres` (`genre_id`, `genres`) VALUES
(1, 'comedy'),
(2, 'horror');

-- --------------------------------------------------------

--
-- Table structure for table `genres_relations`
--

CREATE TABLE `genres_relations` (
  `movie_id` tinyint(11) NOT NULL,
  `genre_id` tinyint(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `genres_relations`
--

INSERT INTO `genres_relations` (`movie_id`, `genre_id`) VALUES
(1, 1);

-- --------------------------------------------------------

--
-- Table structure for table `movies`
--

CREATE TABLE `movies` (
  `movie_id` tinyint(11) NOT NULL auto_increment,
  `movie_title` varchar(255) NOT NULL,
  `tagline` varchar(255) NOT NULL,
  `runtime` tinyint(11) NOT NULL,
  PRIMARY KEY  (`movie_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `movies`
--

INSERT INTO `movies` (`movie_id`, `movie_title`, `tagline`, `runtime`) VALUES
(1, 'Some movie', 'Very nice movie...', 122);
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

That's a very broad question. :)

You will need forms to add/edit/delete rows. Then you will need to mysql_query() your database table. Making use of mysql_connect() and mysql_select_db().

Not sure how much more help I can be.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Post by spamyboy »

Yes, I see you don't realy understood my question...
Here for example I basicly list content of Movies table:

Code: Select all

$query = $moviesSystem->db->query("SELECT * FROM movies LIMIT 10");
while($row=$moviesSystem->db->fetch_array($query)){
print_r(print_r($row)."<br />");
}
Now If I would like to list actors, genres and directors for this movie it would look like:

Code: Select all

$query = $moviesSystem->db->query("SELECT * FROM movies LIMIT 10");
while($row=$moviesSystem->db->fetch_array($query)){
$query_actors = $moviesSystem->db->query("SELECT * FROM actors_relations WHERE movie_id='$row[id]'");
$row_actors=$moviesSystem->db->fetch_array($query);
$query_actors_final = $moviesSystem->db->query("SELECT * FROM actors WHERE actor_id='$row_actors[actor_id]'");
$row_actors_final=$moviesSystem->db->fetch_array($query);
print_r($row_actors_final); // and only here would get our actors list
// same we should do with genres and directors.
print_r(print_r($row)."<br />");
}
(This is only theoricly, so I could made some mistakes.)
So I need help on showing how to simplyfi this.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Yes, if you want to do it in less queries you'll have to look into the JOIN syntax and join your queries on the relational fields (ids).

Also you might be able to use subqueries, if your mysql version supports it.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Post by spamyboy »

That's what I need, I need help with JOIN queries, please give an example for this situation.
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Post by spamyboy »

I'm tryng to do smth like...

Code: Select all

SELECT movies.*, genres.*, genres_relations.*
FROM movies
INNER JOIN genres_relations ON genres_relations.movie_id = movies.movie_id
WHERE movies.movie_id = '1'
but still nothing.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

What you'll need to do is use a JOIN to "join" each table together by their unique identifier:

Code: Select all

SELECT * FROM tbl1
INNER JOIN tbl2 ON tbl1.id=tbl2.tbl1.id
INNER JOIN tbl3 ON tbl2.id=tbl3.id
WHERE condition
This is a very basic query using INNER JOINS. You may want to use LEFT, RIGHT JOINS or whatever you need.

What you must remember is that each table (assuming primary and foreign keys have been set) can be related/linked by corresponsing id's.

Take a look at: http://www.w3schools.com/sql/sql_join.asp
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Descriptive Subjects

Post by feyd »

Please choose a more descriptive subject.
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:2. Use descriptive subjects when you start a new thread. Vague titles such as "Help!", "Why?" are misleading and keep you from receiving an answer to your question.
Post Reply