SQL statements - Corsa Sport - for Vauxhall and Opel Corsa B, Corsa C and Corsa D

Not logged in [Login - Register]

You Are Not Registered Or Not Logged In

Corsa Sport » Message Board » Off Day » Geek Day » SQL statements

New Topic

New Poll

Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.

Jodi_the_g

Member

Registered: 7th Aug 01
Location: Washington D.C
User status: Offline

18th Jan 08 at 15:51

has anyone ever seen ref_annonce?

Ian

Site Administrator

Registered: 28th Aug 99
Location: Liverpool
User status: Offline

18th Jan 08 at 15:53

Had a search - looks more like a field name in some French apps that a bit of syntax.

I assume you don't mean announce?

Either way - thats not a command.

Jodi_the_g

Member

Registered: 7th Aug 01
Location: Washington D.C
User status: Offline

18th Jan 08 at 15:55

yeah its for a french client.

So that makes sense I googled it as well and did not really come up with anything.

Thanks, trying to figure out his statement but as alot of it is french is difficult.

Ian

Site Administrator

Registered: 28th Aug 99
Location: Liverpool
User status: Offline

18th Jan 08 at 15:57

Show me the code?

One I saw was just db structure. I assume it's some sort of announcement flag but I would need to see the app and perhaps some live data to unpick it properly.

Jodi_the_g

Member

Registered: 7th Aug 01
Location: Washington D.C
User status: Offline

18th Jan 08 at 15:57

'I need to insert the field "user_id" from the table "membres2" (the "user_id" n° that corresponds to the member id)

Second, the last number of ref_annonce from the table "annonces". The ad to be inserted will be this number + 1 (last ad: 25 --> ad to be inserted: 26)'

I take it from that he is trying to get the next available free number using it.

Jodi_the_g

Member

Registered: 7th Aug 01
Location: Washington D.C
User status: Offline

18th Jan 08 at 15:59

code:


<?

$link = mysql_connect ($host,$user,$pass) or die ('Erreur : '.mysql_error() );

mysql_select_db($db) or die ('Erreur :'.mysql_error());



$query1 = "SELECT MAX('ref_annonce') FROM annonces";

$result1 = mysql_fetch_assoc($query1);

$ref_annonce = $result1;



$query2="SELECT user_id, ref_entrepr FROM membres2  WHERE user_id=". $_SESSION['user_id'];

$result2 = mysql_query($query2) or die('Erreur SQL !<br />'.$sql.'<br />'.mysql_error());  

$row2 = mysql_fetch_array($result2); 



$ref_entrepr = $row2;



$FormTitre = mysql_real_escape_string(trim($_POST['titre']));

$FormTexte = mysql_real_escape_string(trim($_POST['texte']));

$FormType = $_POST['type'];

$FormProfil = $_POST['profil'];

$FormActivite = $_POST['activite'];

$FormVisibilite = $_POST['visibilite'];





$query = "INSERT INTO annonces(id,ref_entrepr,ref_annonce,titre,type,texte,domaine,activite1,

date_public,date_expire,visible,valid)

VALUES('','$ref_entrepr','$ref_annonce','$FormTitre','$FormTexte','$FormType','$FormProfil','$FormActivite','$Formvisibilite',NOW())";

$result = mysql_query($query);





if(!$result)

{

$feedback ='ERROR mysql';

return $feedback;

}

else

{

echo" Ad successfully inserted";



}



?>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">



<html xmlns="http://www.w3.org/1999/xhtml" >

<head><title>

	Untitled Page

</title></head>

<body>

    <form name="form1" method="post" action="/Files/FileDownload.aspx?FileID=5788" id="form1">

<div>

<input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="/wEPDwUJNzgzNDMwNTMzZGR6HGtWzMA8tr8SRe7WPiDPVStiag==" />

</div>



    <div>

    

    </div>

    </form>

</body>

</html>

Jodi_the_g

Member

Registered: 7th Aug 01
Location: Washington D.C
User status: Offline

18th Jan 08 at 15:59

thats the code he gave me and the instructions in the post above.

Ian

Site Administrator

Registered: 28th Aug 99
Location: Liverpool
User status: Offline

18th Jan 08 at 16:03

Make the ref_annonce field auto_increment and leave it out of the field list in the INSERT statement.

Jodi_the_g

Member

Registered: 7th Aug 01
Location: Washington D.C
User status: Offline

18th Jan 08 at 16:09

quote:
Originally posted by Ian
Make the ref_annonce field auto_increment and leave it out of the field list in the INSERT statement.

So you read it as a field in a table not a command he wants?

I just going to ask for the table information I think.

Ian

Site Administrator

Registered: 28th Aug 99
Location: Liverpool
User status: Offline

18th Jan 08 at 16:19

You don't read it. The app never needs to know the number?

If you write a row, that field will automatically increment (hence the name) - doing probably exactly what you want it to?

Jodi_the_g

Member

Registered: 7th Aug 01
Location: Washington D.C
User status: Offline

18th Jan 08 at 16:21

So you would just have it like that?

I really do not get what he means to do with the user idea and membres2 though.

code:


$link = mysql_connect ($host,$user,$pass) or die ('Erreur : '.mysql_error() );

mysql_select_db($db) or die ('Erreur :'.mysql_error());



$query1 = "SELECT MAX('ref_annonce') FROM annonces";

$result1 = mysql_fetch_assoc($query1);

$ref_annonce = $result1;



$query2="SELECT user_id, ref_entrepr FROM membres2  WHERE user_id=". $_SESSION['user_id'];

$result2 = mysql_query($query2) or die('Erreur SQL !<br />'.$sql.'<br />'.mysql_error());  

$row2 = mysql_fetch_array($result2); 



$ref_entrepr = $row2;



$FormTitre = mysql_real_escape_string(trim($_POST['titre']));

$FormTexte = mysql_real_escape_string(trim($_POST['texte']));

$FormType = $_POST['type'];

$FormProfil = $_POST['profil'];

$FormActivite = $_POST['activite'];

$FormVisibilite = $_POST['visibilite'];





$query = "INSERT INTO annonces(id,ref_entrepr,titre,type,texte,domaine,activite1,

date_public,date_expire,visible,valid)

VALUES('','$ref_entrepr','$FormTitre','$FormTexte','$FormType','$FormProfil','$FormActivite','$Formvisibilite',NOW())";

$result = mysql_query($query);

Jodi_the_g

Member

Registered: 7th Aug 01
Location: Washington D.C
User status: Offline

19th Jan 08 at 13:03

Sorry post above is wrong I have edited it now to

code:


<?

$link = mysql_connect ($host,$user,$pass) or die ('Erreur : '.mysql_error() );

mysql_select_db($db) or die ('Erreur :'.mysql_error());



$query1 = "SELECT MAX('ref_annonce') FROM annonces";

$result1 = mysql_fetch_assoc($query1);

$ref_annonce = $result1;



$query2="SELECT user_id, ref_entrepr FROM membres2  WHERE user_id=". $_SESSION['user_id'];

$result2 = mysql_query($query2) or die('Erreur SQL !<br />'.$sql.'<br />'.mysql_error());  

$row2 = mysql_fetch_array($result2); 



$ref_entrepr = $row2;



$FormTitre = mysql_real_escape_string(trim($_POST['titre']));

$FormTexte = mysql_real_escape_string(trim($_POST['texte']));

$FormType = $_POST['type'];

$FormProfil = $_POST['profil'];

$FormActivite = $_POST['activite'];

$FormVisibilite = $_POST['visibilite'];





$query = "INSERT INTO annonces(id,ref_entrepr,ref_annonce,titre,type,texte,domaine,activite1,

date_public,date_expire,visible,valid)

VALUES('','$ref_entrepr','','$FormTitre','$FormTexte','$FormType','$FormProfil','$FormActivite','$Formvisibilite',NOW



())";

$result = mysql_query($query);





if(!$result)

{

$feedback ='ERROR mysql';

return $feedback;

}

else

{

echo" Ad successfully inserted";



}



?>

Do anyone understand what he means by 'I need to insert the field "user_id" from the table "membres2" (the "user_id" n° that corresponds to the member id)

I have database sturture if that will help really confussed.

Ian

Site Administrator

Registered: 28th Aug 99
Location: Liverpool
User status: Offline

19th Jan 08 at 19:30

Member table has an id field, which he wants to insert on to advert table.

Basically he wants a one-to-many relationship between the member and adverts table, so he has primary key in members and foreign in ads. He also has a primary is ads, which is where I was talking about making the auto_increment field and insert a null, which I think you have done. Not checked the code properly as I've not time to wade through a huge load of syntax when a discussion of the theory would render you able to sort it.

Basically -

SELECT MAX('ref_annonce') FROM annonces

Is a silly way of establishing the annonce id - you never need this number anyway.

To preserve referential integrity, he must make sure he has a valid member number in memory before the advert write. He has this with this select -

SELECT user_id, ref_entrepr FROM membres2

Although he then appears to be dealing with the results array in a scalar context -

$row2 = mysql_fetch_array($result2);

$ref_entrepr = $row2;

$row2 is an associative array - you can't write this direct to the db. You'll just get a text string saying Array[] in the field or if it cast as a non-text data type you might even see an empty string.

Ian

Site Administrator

Registered: 28th Aug 99
Location: Liverpool
User status: Offline

19th Jan 08 at 19:36

ps. docs for mysql_fetch_array are here - http://uk.php.net/mysql_fetch_array

Check example #2

You'll see that the $row variable in this case is also an array (ie. the results of mysql_fetch_array) so when it appears in the printf statement it is given an array index in square brackets.

Array starts at zero, so $row[0] is the first database field (id in that example), $row[1] the second field (name in that example).

Example 2 on that page is in a while loop, yours isn't, because yours is assuming only one row from the database (ie. one member for each id). This should be the case as member id is unique, but I personally always make sure by throwing an ORDER BY id ASC LIMIT 1 on the end of the query to make sure I always get one row containing the lowest id.

Jodi_the_g

Member

Registered: 7th Aug 01
Location: Washington D.C
User status: Offline

22nd Jan 08 at 15:06

I read the example and

code:


$result = mysql_query("SELECT id, name FROM mytable");



while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

    printf("ID: %s  Name: %s", $row[0], $row[1]);  

}



mysql_free_result($result);

I edited it to the following

code:


$result = mysql_query("SELECT user_id, ref_entrepr FROM membres2  WHERE user_id=". $_SESSION['user_id'];



while ($row = mysql_fetch_array($result, MYSQL_NUM))



$ref_entrepr = $row;



$FormTitre = mysql_real_escape_string(trim($_POST['titre']));

$FormTexte = mysql_real_escape_string(trim($_POST['texte']));

$FormType = $_POST['type'];

$FormProfil = $_POST['profil'];

$FormActivite = $_POST['activite'];

$FormVisibilite = $_POST['visibilite'];

Is that correct?

Ian

Site Administrator

Registered: 28th Aug 99
Location: Liverpool
User status: Offline

22nd Jan 08 at 18:49

No - you don't need the while loop as you only have one row.

I assume:

$ref_entrepr = $row[1];

User ID is in $row[0];

Jodi_the_g

Member

Registered: 7th Aug 01
Location: Washington D.C
User status: Offline

22nd Jan 08 at 20:35

CREATE TABLE `annonces` (
`id` int(5) NOT NULL auto_increment,
`ref_entrepr` int(8) unsigned NOT NULL default '0',
`ref_annonce` int(6) unsigned NOT NULL default '0',
`titre` varchar(150) NOT NULL default '',
`type` enum('100','200') NOT NULL default '100',
`texte` text NOT NULL,
`domaine` enum('1','2','3') NOT NULL default '1',
`activite1` int(4) NOT NULL default '0',
`date_public` datetime NOT NULL default '0000-00-00 00

0',
`date_expire` date NOT NULL default '0000-00-00',
`visible` enum('0','1') NOT NULL default '0',
`valid` enum('0','1') NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Structure de la table `membres2`
--

CREATE TABLE `membres2` (
`user_id` int(8) NOT NULL auto_increment,
`user_ref` int(10) NOT NULL default '0',
`user_title` varchar(100) NOT NULL default '',
`categorie` tinyint(1) NOT NULL default '0',
`pays` enum('de','at','be','bg','dk','es','ee','fi','fr','gb','gr','hu','ie','it','lv','lt','lu','mc','no','nl','pl','pt','cz','ro','ru','sk','si','se','ch','ua') NOT NULL default 'de',
`user_name` varchar(25) NOT NULL default '',
`password` varchar(100) NOT NULL default '',
`email` varchar(60) NOT NULL default '',
`remote_addr` varchar(20) NOT NULL default '',
`membre` tinyint(1) NOT NULL default '0',
`nbre_visites` tinyint(2) NOT NULL default '0',
`tentative_hacking` tinyint(2) NOT NULL default '0',
`confirm_hash` varchar(100) NOT NULL default '',
`is_confirmed` set('0','1') NOT NULL default '',
`date_created` varchar(100) NOT NULL default '',
`droits` tinyint(2) NOT NULL default '0',
`valid` set('0','1') NOT NULL default '',
`country` varchar(5) default NULL,
`user_type` varchar(15) NOT NULL default '',
PRIMARY KEY (`user_id`)
) TYPE=MyISAM AUTO_INCREMENT=127 ;

thats the two tables.

New Topic

New Poll