1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

newbie, PHP/MySQL coding help

Discussion in 'PHP' started by Bungeeee, Sep 8, 2013.

  1. #1
    Hello everyone,

    I have a problem regarding PHP/MySQL.
    I've been learning PHP, along with HTML/CSS/jQuery (jQuery just started), for a month now, without any prior programming/markup experience, so be soft here. :p

    Anyways, I've come across a problem I can't solve and I'd be grateful if someone here can solve it for me. I have a leaning more towards the front-end development, so I kinda suck at PHP (haven't really done anything in more than a week), but I would like to know the basics at least. Hope someone answers, thank you in advance.

    Problem:

    Let's say we have a database called 'store', a table called 'movies' and a table called 'reservations'. I need to create a .php page with movie titles (let's say the column is named 'movienames') sorted alphabetically, for example:

    Almost Famous
    American History X
    Apocalipse Now
    .
    .
    .

    I should create a link on every title that leads to another page we can call 'reservations.php'. There, we should list the schedule of reservations for a certain movie. Obviously, there should be a primary key ('movienames') from movies in 'reservations' as a secondary key. We should also have a date column, let's call it 'resDate' and one more secondary key from another table (video store users for example) we can call 'users'. We should format it like this:

    Almost Famous
    Monday, Marko
    Saturday, Eric
    Sunday, Ivan
    .
    .
    .
    That's basically it, I should also have a link on 'reservations.php' that leads to the main page we can call 'index.php'.

    Thx again!

    P.S. I'm sure it's an easy task for you, but I'm really awful at PHP and I neglect it whenever I can.
     
    Bungeeee, Sep 8, 2013 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #2
    You're on the right track.

    both movies and reservations need an "id" column and reservations needs "movie_id" or something similar. Matching on the movie name will be less efficient. Your link to reservations.php just needs to be /reservations.php?movie_id=123. Later on you can look at mod_rewrite but get your basic functionality working first.
     
    sarahk, Sep 8, 2013 IP
  3. RogueCZzzz

    RogueCZzzz Greenhorn

    Messages:
    9
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    13
    #3
    index.php
    
    mysql_connect('localhost','username','password') or die(mysql_error());
    mysql_select_db('dbName');
    
    $query = mysql_query('SELECT * FROM movies ORDER BY name ASC') or die(mysql_error());
    
    
    
    while($row = mysql_fetch_assoc($query)){
    
        echo '<a href="/reservation.php?movieid='.$row['id'].'">'.$row['name'].'</a>';
        echo '<br>';
    }
    PHP:
    reservation.php
    
    mysql_connect('localhost','username','password') or die(mysql_error());
    mysql_select_db('dbName');
    
    $id = (int) $_GET['movieid'];
    
    $queryMovie = mysql_query('SELECT * FROM movies WHERE id ='.$id.' LIMIT 1');
    $queryReservation = mysql_query('SELECT * FROM reservation WHERE mid ='.$id.' ORDER BY id DESC');
    
    $countReservation = mysql_num_rows($queryReservation);
    
    
    $rowMovie = mysql_fetch_assoc($queryMovie);
    
    echo '<a href="/reservation.php?movieid='.$rowMovie['id'].'">'.$rowMovie['name'].'</a><br />';
    
    if($countReservation == 0){
        echo 'There are no reservations <br />';
    }
    
    while ($rowReservation =  mysql_fetch_assoc($queryReservation))  {
        echo $rowReservation['datetime'];
        echo ' | '. $rowReservation['username'];
        echo "<br>";
    }
    
    echo '<a href="/index.php">Back </a>';
    PHP:
    database mysql
    
    SET NAMES utf8; 
    SET foreign_key_checks = 0; 
    SET time_zone = '+02:00'; 
    SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; 
    
    
    DROP TABLE IF EXISTS `movies`; 
    CREATE TABLE `movies` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) 
    ) 
    ENGINE=InnoDB DEFAULT 
    CHARSET=utf8; 
    
    INSERT INTO `movies` (`id`, `name`) VALUES 
    (1, 'Almost Famous'), 
    (2, 'American History X'), 
    (3, 'Apocalipse Now'); 
    
    DROP TABLE IF EXISTS `reservation`; 
    
    CREATE TABLE `reservation` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `mid` int(11) NOT NULL, 
    `username` varchar(50) COLLATE utf8_czech_ci NOT NULL, 
    `datetime` datetime NOT NULL, PRIMARY KEY (`id`) 
    ) 
    ENGINE=InnoDB DEFAULT 
    CHARSET=utf8;
    
    INSERT INTO `reservation` (`id`, `mid`, `username`, `datetime`) VALUES 
    (1, 1, 'Marko', '2013-09-18 20:44:18'), 
    (2, 1, 'John', '2013-09-18 20:44:55'), 
    (3, 1, 'Eric', '2013-09-18 21:08:48');
    
    Code (markup):
     
    Last edited: Sep 18, 2013
    RogueCZzzz, Sep 18, 2013 IP
  4. EmmanuelFlossie

    EmmanuelFlossie Active Member

    Messages:
    159
    Likes Received:
    11
    Best Answers:
    2
    Trophy Points:
    65
    #4
    @RogueCZzzz
    Change all mysql to mysqli. You need to stop using the old standards.

    myslqi
     
    EmmanuelFlossie, Sep 18, 2013 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #5
    In post #3 are you showing us your progress? We're not teachers marking your work. You need to tell us what issues you are having or what you want discussed.
     
    sarahk, Sep 18, 2013 IP
  6. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #6
    Why would you recommend mysqli if you want to avoid old standards? Use PDO
     
    PoPSiCLe, Sep 18, 2013 IP
  7. EmmanuelFlossie

    EmmanuelFlossie Active Member

    Messages:
    159
    Likes Received:
    11
    Best Answers:
    2
    Trophy Points:
    65
    #7
    Because PDO is OOP only.
     
    EmmanuelFlossie, Sep 18, 2013 IP
  8. ThePHPMaster

    ThePHPMaster Well-Known Member

    Messages:
    737
    Likes Received:
    52
    Best Answers:
    33
    Trophy Points:
    150
    #8
    Like SarahK said, what issue are you having? Just by looking at the code everything seems intact.
     
    ThePHPMaster, Sep 18, 2013 IP
  9. RogueCZzzz

    RogueCZzzz Greenhorn

    Messages:
    9
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    13
    #9
    I´m not showing you my progress i just wanted help to bungeeee how he can start with his question. Why you answering to me, i´m not the one who created this thread...
     
    RogueCZzzz, Sep 19, 2013 IP
    sarahk likes this.
  10. ThePHPMaster

    ThePHPMaster Well-Known Member

    Messages:
    737
    Likes Received:
    52
    Best Answers:
    33
    Trophy Points:
    150
    #10
    Sorry, I totally thought it was the same person, gotta open my eyes more! Your code looks solid to me.
     
    ThePHPMaster, Sep 19, 2013 IP
    sarahk likes this.
  11. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #11
    And your point? It's perfectly usable in a non-OOP setting? And it's imensely more secure than both mysql_ and mysqli_ straight out of the box, due to how you have to use it. That was my point. And, besides, it's not a stupid thing to learn, given that it's also quite a lot more powerful (if used properly) than mysql*
     
    PoPSiCLe, Sep 20, 2013 IP
  12. EmmanuelFlossie

    EmmanuelFlossie Active Member

    Messages:
    159
    Likes Received:
    11
    Best Answers:
    2
    Trophy Points:
    65
    #12
    Absolutely correct, I was only suggesting adding the i because not everybody can write or want to write non procedural.

    So this would be the quickest option for procedural methods :)
     
    EmmanuelFlossie, Sep 21, 2013 IP