Create MySQL table

Hello,

How to create a mysql table from this php code. I'm super noobs in MySQL, sorry...

// snippet of code to see if a user has downloaded too much

$config['download_limit'] = 3;

// check database for their IP
$sql = "SELECT count(*) AS number FROM ".$db->pre."downloads
          WHERE ip='$_SERVER[REMOTE_ADDR]'
          AND category='games'";

$iplocked = $db->query_first($sql);

// check to see if IP and/or cookie is over the limit
if($iplocked['number'] >= $config['download_limit'] || 
$_COOKIE['download_limit'] >= $config['download_limit']){
    echo "over user limit error";
    // YOU: exit or redirect them
}
// they're good to go. allow download
else{
    // insert their ip into database
    $data = array('ip'=>$_SERVER['REMOTE_ADDR'], 'time'=>time(), 'category'=>"games");
    $db->query_insert("downloads", $data); 

    // set a cookie too
    setcookie("download_limit", ($_COOKIE['download_limit']+1), time() + 24 * 3600);

    // YOU: send the user to the file
}


// deletes "expired" entries each time script is run
$lockTime = time()-24*3600; // 24hrs
$sql = "DELETE FROM ".$db->pre."downloads
          WHERE category='games'
          AND time < $lockTime";
$db->query($sql); 

PHP:

I found this nice code from here: http://www.ricocheting.com/scripts/php_mysql_wrapper.php

After 2 hour learning from tizag.com and after couple of try I give up :/

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Create a MySQL table in the selected database
mysql_query("CREATE TABLE downloads(
ip INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
 count INT), 
category VARCHAR(30), 
 age INT)")
 or die(mysql_error());  

echo "Table Created!";

?>

PHP:

Thanks in advance

 

CREATE TABLE `downloads` (
  `ip` int(11) NOT NULL auto_increment,
  `category` varchar(30) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY  (`ip`)
)

Code (markup):

That should be all you need to have for your create database. Also, note that there is no count since the SQL query in the first code you provided generates the count upon querying the database

 

Hello,

I try and run it in MySQL and I'm geting this error:

Database Error 
Message: MySQL Query fail: INSERT INTO `downloads` (`ip`, `time`, `category`) VALUES ('60.54.68.225', '1220677948', 'games'); 

MySQL Error: Duplicate entry '60' for key 1 

Date: Saturday, September 6, 2008 at 1:12:28 AM 

Script: /demo/ 

Code (markup):

I think age is not needed...? Any idea?

Thanks

 

Hi,

After a couple of try I come up with this and run it in phpmyadmin

CREATE TABLE `downloads` (
  `ip` int(11) NOT NULL auto_increment,
  `time` TIME NOT NULL,
  `category` varchar(30) NOT NULL,
  PRIMARY KEY  (`ip`)
)

Code (markup):

and now im getting this error:

Database Error
Message: Result ID: 1 could not be freed. 
Date: Saturday, September 6, 2008 at 1:48:26 AM 
Script: /demo/index.php 

Code (markup):

any idea? Thanks

 

CREATE TABLE `downloads` (
  `id` int(11) auto_increment,
  `ip` varchar(15) NOT NULL,
  `category` varchar(30) NOT NULL,
  `time` TIME NOT NULL,
  PRIMARY KEY  (`id`)
)

Code (markup):

Try that

 

Hi,

After run your code in my sql I'm still getting the same error




anyway I will take a look at my code again and see anything wrong.

Many thanks for your help

 

Well I used the create table in a MySQL console, and also used the exact insert that you had...it works fine for me. Here are the screenshots
Console


Table contents

 

Hi,

You use the code above without changing anything

<? 
// every page needs to start with these basic things

// I'm using a separate config file. so pull in those values
require("config.inc.php");

// pull in the file with the database class
require("database.class.php");

// create the $db ojbect
$db = new Database($config['server'], $config['user'], 
                   $config['pass'], $config['database'], $config['tablePrefix']);

// connect to the server
$db->connect();

// snippet of code to see if a user has downloaded too much

$config['download_limit'] = 3;

// check database for their IP
$sql = "SELECT count(*) AS number FROM ".$db->pre."downloads
          WHERE ip='$_SERVER[REMOTE_ADDR]'
          AND category='games'";

$iplocked = $db->query_first($sql);

// check to see if IP and/or cookie is over the limit
if($iplocked['number'] >= $config['download_limit'] || 
$_COOKIE['download_limit'] >= $config['download_limit']){
    echo "over user limit error";
    // YOU: exit or redirect them
}
// they're good to go. allow download
else{
    // insert their ip into database
    $data = array('ip'=>$_SERVER['REMOTE_ADDR'], 'time'=>time(), 'category'=>"games");
    $db->query_insert("downloads", $data); 

    // set a cookie too
    setcookie("download_limit", ($_COOKIE['download_limit']+1), time() + 24 * 3600);

    // YOU: send the user to the file
}


// deletes "expired" entries each time script is run
$lockTime = time()-24*3600; // 24hrs
$sql = "DELETE FROM ".$db->pre."downloads
          WHERE category='games'
          AND time < $lockTime";
$db->query($sql); 

// and you're done, remember to close connection

$db->close(); 

?>

PHP:

 

No. I was just showing that the query for the table creation, and the insertion of multiple ip's that are the same work. Like it says above, it was done in a console window

 

thanks for the help + rep added

 

You can use following link to read how to create table using php code.
http://www.programming-web.com/index.php?option=com_content&view=article&id=10:connecting-to-mysql-server-using-php&catid=4hp-basics&Itemid=10