Need help in building a database

[Joe Cool]

Hi, I'm new to the forum and this is my first post. I hope you can help me out.

 

I'm currently working with a client who has asked me to make a few changes to his existing website. He asked me to help him move to a new hosting company, overhaul the SEO of the site and make some changes to the content. He has also changed the company name and wants me to rebrand it from x to y.

 

The site was created using php and it's not something I have experience of. However, that doesn't matter because I can make all the changes he asked for without touching the php coding. The problem is one section of the site won't function without the database that is associated with it.

 

The client doesn't have a copy of the site or the database. Luckily I downloaded a copy of the site before the hosting expired. However, when I downloaded a copy of the site (using ftp) from the previous host, the mysql database wasn't stored with the rest of the site. The old host refused to send my client a copy of the database and claimed they deleted it. I contacted the original web designer and he said he created the database on the server using php my admin and that he doesn't have a copy of the database. He also said the database itself was fairly small and only contained a few lines of code.

 

He thinks it would be relatively straightforward to recreate the database by looking at the php code.

 

I was wondering if I posted the code from the two pages of the website affiliated with the database if you could help me to recreate the mysql database? I have no experience of building databases and the client doesn't have any technical knowledge.

 

Any help you could give me would be greatly appreciated.

 

Regards,

 

Joe.

[kickstart]

Hi

 

If it really is simple then it should be possible, but testing will be interesting.

 

I would be more concerned with the contents of the database.

 

Post up the sections of the pages which read / write to the database.

 

By the way, if you haven't used php in the past then it is probably worth pointing out that you can download something like wampserver which includes apache, php and mysql to do the development on your own machine.

 

All the best

 

Keith

[Joe Cool]

Hi Keith, thanks for your reply.

 

Here is the code from the first page: (the file db.php connects to the database and obviously isn't the database itself). I've written company x instead of the actual company name. Nothing else has been changed.

 

<?php
include ('includes/db.php');
    
    $query = "SELECT * FROM `carandride` where `show` = 'yes' order by `destination` asc";
    $result = mysql_query($query) or die(mysql_error());
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($line as $col_value) {
    $x++;
    if ($x == 1) { $id = $col_value; }
    if ($x == 2) { $ldestination = $col_value; }
    if ($x == 3) { $lstart = $col_value; }
    if ($x == 4) { $journeyinfo = $col_value; }
    if ($x == 5) { $journeyinfo2 = $col_value; }
    if ($x == 6) { $journeyurl = $col_value; }
    if ($x == 7) { $journeyurl2 = $col_value; }
    if ($x ==  { $show = $col_value; }
    }
    
    $routes .= "<a href=\"carandridesub.php?destination=$ldestination&start=$lstart\" class=\"foot2\" title=\"travel to $ldestination from $lstart\">$lstart to $ldestination</a>, ";
    
    $x = 0;
    }    
    

if ($start && $description) {
$title = "Car and Ride - Company x - Travel from $start to $destination in the UK";
    $keywords = "$start, $destination, $journeyinfo, Company x car and ride, travel by plane, travel by train, travel by car, travel by taxi, door to door, uk airports, event venues, air flight, rail, coach, birmingham, birmingham nec, birmingham international airport, bournemouth, brighton, bristol, cambridge, gatwick airport, heathrow airport, leeds, manchester, newcastle city, newcastle upon tyne, newport, reading, southampton";
    $description = "Travel from $start to $destination, $journeyinfo. Copany x offer door to door travel to various uk destinations such as Gatwick airport and Heathrow airport in London, Manchester, Newcastle and Birmingham.";
} else {
$title = "Car and Ride - Company x - Travel Door to Door in the UK";
    $keywords = "Company x car and ride, travel by plane, travel by train, travel by car, travel by taxi, door to door, uk airports, event venues, air flight, rail, coach, birmingham, birmingham nec, birmingham international airport, bournemouth, brighton, bristol, cambridge, gatwick airport, heathrow airport, leeds, manchester, newcastle city, newcastle upon tyne, newport, reading, southampton";
    $description = "Company x offer door to door travel to various uk destinations such as Gatwick airport and Heathrow airport in London, Manchester, Newcastle and Birmingham.";
}

$footkey = "<strong>Company x car and ride routes:</strong> 
    $routes";
    $page = "carandride";
       
    $headex = "<script language=\"javascript\"> 
    function setOptions(chosen) { 
        var selbox = document.carandride.start; 
        selbox.options.length = 0; 
        if (chosen == \" \") { 
            selbox.options[selbox.options.length] = new Option('--','Select a Destination'); 
     ";
    
        	$curdest = "";
            $query = "SELECT * FROM `carandride` where `show` = 'yes' order by `destination` asc";
            $result = mysql_query($query) or die(mysql_error());
            while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
            foreach ($line as $col_value) {
            $x++;
            if ($x == 1) { $id = $col_value; }
            if ($x == 2) { $destination = $col_value; }
            if ($x == 3) { $start = $col_value; }
            if ($x == 4) { $journeyinfo = $col_value; }
            if ($x == 5) { $journeyinfo2 = $col_value; }
            if ($x == 6) { $journeyurl = $col_value; }
            if ($x == 7) { $journeyurl2 = $col_value; }
            if ($x ==  { $show = $col_value; }
            }
            
            if ($curdest != "$destination") {      
            $headex .= "} \n";
            $headex .= "if (chosen == \"$destination\") { \n";
            $headex .= "selbox.options[selbox.options.length] = new Option('Select One','Select One'); \n";
            $curdest = "$destination";            
            }
            
            $headex .= "selbox.options[selbox.options.length] = new Option('$start','$start'); \n";
            
            $x = 0;
            }
            
            
    $headex .= "    }
        }
	</script>";
    
    
include ('includes/head.php');
?>


<h1>Company x Car and Ride</h1>

<div class="maincont">

Going on a long distance Journey?  Now you can Save Money and Get their Quicker. Travel Door to 
    Door, without taking your car all the way. Combined long distance travel with Company x Car and Ride.
    
    <br />
    <br />
    
    <h2>Step One</h2>
    <br />
    Make your choice from a selection of destinations. At the start of your Journey; Drive, be dropped off or get a taxi to one of the many Car and Ride Boarding points.
    <br /><br />

    <h2>Step Two</h2>
    <br />
    At the Car and Ride boarding point you combine your journey, by parking your car and linking to one of the following applicable direct services which you pre-book with us; 
<br /><br />
    1. UK Internal Air Flight<br />
    2. Train<br />
    3. Coach
    <br /><br />

    <h2>Step Three</h2>
    <br />
When you arrive at your destination, should you require we provide onward taxi travel at the Station or Terminal ensuring that you are whisked off to your door step location. You will be given an option to book this after booking STEP 3. 
<br /><br />
Each day we are adding new routes which have been packaged together and identified as a better option than travelling by car. I suppose you could say it is like park and ride... only on a much bigger scale.
    
    <br /><br /><br />
    
    <div style="width: 80%; border: 1px solid #bbbbbb; background-color: #dddddd; padding: 3px;">
<form id="carandride" name="carandride" method="get" action="carandridesub.php" style="display: inline;">
    <table width="100%" border="0" cellspacing="10" cellpadding="0">
      <tr>
        <td width="48%">
            <strong>Where do you want to go?</strong>
    		<br /><br />
            <select name="destination" style="margin-top: 5px;" size="1" onchange="setOptions(document.carandride.destination.options[document.carandride.destination.selectedIndex].value);"> 
                <option value=" " selected="selected">Select One</option> 
                <?php
                    $curdest = "";
                    $query = "SELECT * FROM `carandride` where `show` = 'yes' order by `destination` asc";
                    $result = mysql_query($query) or die(mysql_error());
                    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
                    foreach ($line as $col_value) {
                    $x++;
                    if ($x == 1) { $id = $col_value; }
                    if ($x == 2) { $destination = $col_value; }
                    if ($x == 3) { $start = $col_value; }
                    if ($x == 4) { $journeyinfo = $col_value; }
                    if ($x == 5) { $journeyinfo2 = $col_value; }
                    if ($x == 6) { $journeyurl = $col_value; }
                    if ($x == 7) { $journeyurl2 = $col_value; }
                    if ($x ==  { $show = $col_value; }
                    }
                    
                    if ($curdest != "$destination") {            
                    print "<option value=\"$destination\">$destination</option>\n";
                    $curdest = "$destination";            
                    }
                    
                    $x = 0;
                    }
                ?>
          </select>
        </td>
        <td width="46%">
            <strong>At the Start of your Journey get a Car to:</strong>
            <br />
            <select name="start" style="margin-top: 5px;" size="1"> 
                <option value=" " selected="selected">Select a Destination</option> 
            </select>
         </td>
        <td width="6%" align="right"><br /><br />
          <input type="submit" value="Go >" style="margin-top: 5px; cursor: pointer; cursor: hand;" /></td>
      </tr>
    </table>
    </form>
    </div>
    
    
    <br /><br />
    
    Book with us before the end of this month and Save £5 on Subscription to Company x Car and 
    Ride bookings.
<br />
<br />
    Subscription: Now only £3.95 per booking (Normally £8.95 per booking)
<br /><br />
    To get full access on journey details you will need to subscribe, otherwise you will get restricted access only.
   
         
</div>


<?php
include ('includes/foot.php');
?>

 

 

[Joe Cool]

This is the code from page 2:

 

<?php
include ('includes/db.php');
    
    $query = "SELECT * FROM `carandride` where `show` = 'yes' order by `destination` asc";
    $result = mysql_query($query) or die(mysql_error());
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($line as $col_value) {
    $x++;
    if ($x == 1) { $id = $col_value; }
    if ($x == 2) { $dest = $col_value; }
    if ($x == 3) { $star = $col_value; }
    if ($x == 4) { $journeyinfo = $col_value; }
    if ($x == 5) { $journeyinfo2 = $col_value; }
    if ($x == 6) { $journeyurl = $col_value; }
    if ($x == 7) { $journeyurl2 = $col_value; }
    if ($x ==  { $show = $col_value; }
    }
    
    $routes .= "<a href=\"carandridesub.php?destination=$dest&start=$star\" class=\"foot2\" title=\"travel to $dest from $star\">$star to $dest</a>, ";
    
    $x = 0;
    }    
    

if ($start && $destination) {

    $query = "SELECT * FROM `carandride` where `show` = 'yes' and `start` = '$start' and `destination` = '$destination' order by `destination` asc";
    $result = mysql_query($query) or die(mysql_error());
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($line as $col_value) {
    $x++;
    if ($x == 4) { $journeyinfo = $col_value; }
    }
    
    $routes .= "<a href=\"carandridesub.php?destination=$dest&start=$star\" class=\"foot2\" title=\"travel to $dest from $star\">$star to $dest</a>, ";
    
    $x = 0;
    } 


$title = "Car and Ride - company x - Travel from $start to $destination in the UK";
    $keywords = "$start, $destination, $journeyinfo, company x car and ride, travel by plane, travel by train, travel by car, travel by taxi, door to door, uk airports, event venues, air flight, rail, coach, birmingham, birmingham nec, birmingham international airport, bournemouth, brighton, bristol, cambridge, gatwick airport, heathrow airport, leeds, manchester, newcastle city, newcastle upon tyne, newport, reading, southampton";
    $description = "Travel from $start to $destination, $journeyinfo. company x offer door to door travel to various uk destinations such as Gatwick airport and Heathrow airport in London, Manchester, Newcastle and Birmingham.";
} else {
$title = "Car and Ride - company x - Travel Door to Door in the UK";
    $keywords = "company x car and ride, travel by plane, travel by train, travel by car, travel by taxi, door to door, uk airports, event venues, air flight, rail, coach, birmingham, birmingham nec, birmingham international airport, bournemouth, brighton, bristol, cambridge, gatwick airport, heathrow airport, leeds, manchester, newcastle city, newcastle upon tyne, newport, reading, southampton";
    $description = "Company x offer door to door travel to various uk destinations such as Gatwick airport and Heathrow airport in London, Manchester, Newcastle and Birmingham.";
}
$footkey = "<strong>Company x car and ride routes:</strong> 
    $routes";
    $page = "carandride";
    
include ('includes/head.php');
?>


<h1>Car and Ride</h1>

<div class="maincont">
    
<table width="90%" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td><div align="center"><strong>Car to:</strong> <br />
          <? echo $start ?> </div></td>
        <td><div align="center"><img src="images/arrows.gif" width="68" height="20" /></div></td>
        <td><div align="center"><strong>Ride to:</strong> <br />
          <? echo $destination ?> </div></td>
      </tr>
    </table>
<br />
<br />
    
    Before you can take advantage of our Car and Ride service you will need to subscribe to Company x.
    <br />
    <br />
    Book with us before the end of this month and Save £5 on Subscription to Company x Car and 
    Ride bookings.
<br />
<br />
    Subscription: Now only £3.95 per booking (Normally £8.95 per booking).
    <br /><br />
    We accept PayPal, Credit Card and Debit Card payments via PayPal.
    <br /><br /><br />
    
    <div align="center">
<form action="https://www.paypal.com/cgi-bin/webscr" method="post">
<input type="hidden" name="cmd" value="_s-xclick">
<input type="hidden" name="hosted_button_id" value="479179">
<input type="image" src="https://www.paypal.com/en_GB/i/btn/btn_buynowCC_LG.gif" border="0" name="submit" alt="">
<img alt="" border="0" src="https://www.paypal.com/en_GB/i/scr/pixel.gif" width="1" height="1">
</form>

    </div>
   
         
</div>


<?php
include ('includes/foot.php');
?>  

[Joe Cool]

What do you think? Is it possible to recreate the database using this code?

[kickstart]

Hi

 

Appears to be a very limited number of columns and only a single table.

 

CREATE TABLE `yourdatabasename`.`carandride` (
`Id` INT NOT NULL AUTO_INCREMENT ,
`Destination` VARCHAR( 50 ) NOT NULL ,
`Start` VARCHAR( 50 ) NOT NULL ,
`JourneyInfo` VARCHAR( 255 ) NOT NULL ,
`JourneyInfo2` VARCHAR( 255 ) NOT NULL ,
`JourneyUrl` VARCHAR( 50 ) NOT NULL ,
`JourneyUrl2` VARCHAR( 50 ) NOT NULL ,
`Show` VARCHAR( 3 ) NOT NULL ,
PRIMARY KEY ( `Id` ) 
) ENGINE = InnoDB[/quote]

All the best

Keith

[Joe Cool]

Hi Keith, many thanks for your reply. It was a great help and almost did the trick.

 

Using your code I managed to recreate the database for the car and ride section. This section can now be accessed online. However, the problem is that the contents of the two drop down menus 'destination' and 'start' are empty.

 

This obviously means the user can't select a destination or stipulate where they want to go. I think you foreseen this problem in your first post when you said: "I would be more concerned with the contents of the database."

 

Do the options for these menus have to be added to the database directly? If so, how would I go about doing that?

 

Let's say for the sake of argument I wanted to have London as an option in the 'destination' menu and Liverpool as an option in the 'start' menu, how would I add that to the database?

 

 

[kickstart]

Hi

 

Good question, as it would appear that the table is meant to have a line per possible journey (ie, start and destination). The code appears to remove the duplicates in a very crude way (ie, gets all the data back then drops the duplicates, rather than just not bringing back duplicates)

 

I would put in a row for each possible journey, with a start and end point, some random info and set Show to "yes" (although I would say it would be better to use a boolean field on the database and check for 1 or 0 rather than yes or no). Also try a few set to something other than "yes" to make sure they are not displayed.

 

I would hope that the person whose web site it is has a seperate list of the possible journies.

 

All the best

 

Keith

[Joe Cool]

Hi Keith, thanks for your reply.

 

My client should have a list of the possible journeys. As you know, the website has a drop down menu that lets the user choose from a list of possible destinations and another drop down menu that lets the user choose from a list of possible starting points. If I could add one option to the 'start' menu and one option to the 'destination' menu then I could replicate that for other entries. For example start at Liverpool and go to London.

 

Could I do this using the interface on php myadmin? I'm new to both php and databases and I don't really know where to start.

 

On php myadmin the headings for the database are Field, Type, Function, Null and Value.

 

The eight Field names are: Id, Destination, Start, JourneyInfo, JourneyInfo2, JourneyUrl, JourneyUrl2 and Show.

 

The eight corresponding values for Type are: int(11), varchar(50), varchar(50), varchar(255), varchar(255), varchar(50), varchar(50) and char(3).

 

The header 'Function' has eight drop-down menus with a range of options on each, but the default option for each is blank.

 

There's nothing written under the 'Null' header. There's just eight blank boxes.

 

The 'Value' header has eight empty text entry boxes corresponding to the values set in the 'Type' section.

 

Of course you know all that already since you provided the code for the database. I'm just wondering how I would go about adding the start and destination points to that structure? Would I have to alter some of the values in the 'start' and 'destination' fields or would I create a new row with new values? If I did that, how would I link the new row to the 'start' and 'destination' fields, so that the options such as Liverpool and London appear on the drop down lists on the website?

 

At the moment I want to try and recreate the previous structure.

 

[kickstart]

Hi

 

Phpmyadmin has an insert function.

 

If you go into phpmyadmin, select the database, then select the table. Then you will see a list of functions along the top, one of which is SQL (which you could have pasted in the table create I posted earlier to actually create the table), and another is insert.

 

Click on insert an it will give you a panel with (by default) fields to enter the data for 2 rows. Type in the data and away you go.

 

All the best

 

Keith

[Joe Cool]

Thanks Keith. I'll play about with it and see how I get on.