Page 1 of 1

Help with sql / php

Posted: Thu Oct 04, 2007 1:39 pm
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);

Posted: Thu Oct 04, 2007 1:41 pm
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.

Posted: Thu Oct 04, 2007 1:52 pm
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.

Posted: Thu Oct 04, 2007 1:58 pm
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.

Posted: Thu Oct 04, 2007 2:02 pm
by spamyboy
That's what I need, I need help with JOIN queries, please give an example for this situation.

Posted: Fri Oct 05, 2007 8:01 am
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.

Posted: Fri Oct 05, 2007 9:49 am
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

Descriptive Subjects

Posted: Fri Oct 05, 2007 10:58 am
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.