stuck on sqlite primary key

Lluvia

Group: Members
Posts: 40
Joined: 03-August 12
Expertise:Graphics,Flash

Posted 17 August 2012 - 02:16 PM (#1)

stuck on sqlite primary key

gives me syntax error :(

Hello everybody, I hope somebody would help me with this. I'm making a database in sqlite with php. Everything was going well, until I tried to update the users, and it gave me a glitch. The new user name for the update, replaced the current session user and messed all the displayed data. So I figured I could use an id number to compare and replace the data only if the id number would match. But....trying to make this id number a primary key that would autoincrement has not been posible for me

. I was up all night trying to figure out, googling al over. But every answer would give me syntax error.

here is the link to the site

http://www.mazatlano...luvia/systemays

and here is the code to prepare the database

//CREAR UNA TABLA DE USUARIOSIST==============================
		//Abrir conexion-----------------------------
$conexion = sqlite_open('sistemays.db') or die('No se pudo realizar la conexion');
	//Crear tabla--------------------------------
$consulta = 
<<<SQL
id Int Not Null,
Primary Key(id),
usuario Char(40) Not Null,
contrasena Char(40) Not Null,
nivel Char(10) Not Null,
quienAgrego Char(40) Not Null,
nombres Char(40) Not Null,
apellido1 Char(40) Not Null,
apellido2 Char(40) Not Null,
direccion Char(150) Not Null,
telefono Char(60) Not Null,
edad Int,
email Char(100),
ingresoMes Int,
acumulado Int,
realizoPago Char(30),
permisos Int,
idActualizar Int
);
SQL;
	//insertar contenido en la tabla-------------
$resultado = sqlite_exec($conexion,$consulta);
	//cerrar conexion----------------------------
sqlite_close($conexion);

Sorry about the language of the program and is my first topic. I hope I gave you enough info.
Thanks

Cyril

Group: Members
Posts: 2545
Joined: 03-August 10
Expertise:HTML,CSS,PHP,Javascript,Graphics

Posted 17 August 2012 - 02:19 PM (#2)

Use SQLite 3! (Don't worry, it's installed by default).

As usual, PHP's global functions are outdated; I had issues with it as well, and it turns out that the

sqlite_

methods are actually for SQLite 2.

website :: github :: twitter :: dribbble :: ~~forrst~~
html, css, php, javascript, graphics

Lluvia

Group: Members
Posts: 40
Joined: 03-August 12
Expertise:Graphics,Flash

Posted 17 August 2012 - 02:37 PM (#3)

Cyril, on 17 August 2012 - 02:19 PM, said:

Use SQLite 3! (Don't worry, it's installed by default).

As usual, PHP's global functions are outdated; I had issues with it as well, and it turns out that the

sqlite_

methods are actually for SQLite 2.

Thanks for the quick reply Cyril! I'm completely new on PHP, I thought I was doing sqlite 3, how do I change my code into 3?

Forget about the previous response, I did not notice the link on sqlite3 before :rolleyes:

thanks I'll check it out

Lluvia

Group: Members
Posts: 40
Joined: 03-August 12
Expertise:Graphics,Flash

Posted 17 August 2012 - 03:44 PM (#4)

I DID IT! It works now, I kind of made my own thing. Could not make that primary key auto increment work. But I did this in the code where I add my users.

$conexion = sqlite_open('sistemays.db');
$consulta = "SELECT id FROM usuariosist WHERE id = (SELECT MAX(id) FROM usuariosist)";
$resultado = sqlite_query($conexion,$consulta);	
while($fila = sqlite_fetch_array($resultado)){
$id = $fila['id']+ 1;
}

And now, every time we add a user it increments and I have my Id. Now when I update de data of the users it does not mess up my current user in the session!

If you want to check it out, user is lluvia
password is jbk

Cyril

Group: Members
Posts: 2545
Joined: 03-August 10
Expertise:HTML,CSS,PHP,Javascript,Graphics

Posted 17 August 2012 - 03:51 PM (#5)

... that's because auto-increment doesn't work/exist in SQLite2.
So, use SQLite3

Honestly, the code you're using right now is really inefficient and will be hell to maintain. The

sqlite_

methods have even been removed from PHP 5.4! You might as well use the built-in SQLite3 class.

Edit: In fact, you should use PDO, as it supports SQLite as well

It will also make it a breeze to switch from SQLite to MySQL if you ever need to

website :: github :: twitter :: dribbble :: ~~forrst~~
html, css, php, javascript, graphics

Lluvia

Group: Members
Posts: 40
Joined: 03-August 12
Expertise:Graphics,Flash

Posted 17 August 2012 - 04:26 PM (#6)

Cyril, on 17 August 2012 - 03:51 PM, said:

... that's because auto-increment doesn't work/exist in SQLite2.
So, use SQLite3

Honestly, the code you're using right now is really inefficient and will be hell to maintain. The

sqlite_

methods have even been removed from PHP 5.4! You might as well use the built-in SQLite3 class.

Edit: In fact, you should use PDO, as it supports SQLite as well

It will also make it a breeze to switch from SQLite to MySQL if you ever need to

You are right Cyril, and don't think that I wont switch from sQlite to sQlite3. I just had to show something to my client today! :unsure:

At least I can show him something for now. And make the switch over the week or so. Thanks for recomending PDO, I was just reading about it! I seem's like the fastest aproach!