MySQL Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

MySQL Help

Post by Devnull »

I got two tables, one named 'user' and the other 'reports'. In both tables there is a field called 'province'. I have a form where a user has to type in some information and it is inserted into the reports table.

Like I mentioned above, the 'reports' table has a 'province' field. I want to write a function where it queries the 'user' table and gets the user's province and when the user fills out the form and hits submit, the province of the user is the same province that he/she inserted (I don't want the user to write in a province, they're already assigned one).

For example, I havae user whos username is demo, demo's province is Madrid, when demo logs into the system and fills out the form and hits submit; in the 'reports' table there is a 'province' field, this should be Madrid (It's automatic). Now let's say we have demo2, demo2's province is Andalucia, when demo2 fills out the form and hits submit, Andalucia should be added to the new row in the 'informes' table under the 'province' field.
User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

Post by Devnull »

This is the function I have written out so far, I'm having problems in the MySQL query ($result). Basically, I'm stuck and I don't know what more code I have to add to do this.

I want the final province variable to be $province. E.g. so if demo's province was Madrid, the variable would be $province = 'Madrid';

Code: Select all

function get_province() {
	$conn = db_connect();
	if (!$conn)
		return false;
	
	$result = mysql_query("select * from user
									and where province = '$province'");
	if (!result)
		return false;
	if (mysql_num_rows($result)>0)
		return true;
	else
		return false;
}
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Code: Select all

function get_province() { 
   $conn = db_connect(); 
   if (!$conn) 
      return false; 
    
   $result = mysql_query("select * from user 
                           and where province = '$province'"); 

// added a $ before result here
   if (!$result) 
      return false; 
   if (mysql_num_rows($result)>0) 
      return true; 
   else 
      return false;

// here's a line to extract the row from the db
$array = mysql_fetch_assoc($result);

// store the province in $province
$province = $array['province'];
}
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Devnull
Forum Commoner
Posts: 52
Joined: Fri Oct 22, 2004 2:19 pm

Post by Devnull »

Thanks. But does this detect which user it is, I notices you are using an array so wouldn't that mean it's taking all the provinces out. Let's say the user demo has ID 1 and user demo2 has ID 2. Both of their provinces are different, does this code detect that ?

Basically, what I want is simple, below are the structures of my two tables, this may help you out. I want the system to detect which user is inputting the data (I'm using sessions) and then I want it to query the 'user' table to check the province and set this as a variable, that's all.

Code: Select all

CREATE TABLE `informes` (
  `id` tinyint(4) NOT NULL auto_increment,
  `date` date NOT NULL default '0000-00-00',
  `city` tinytext NOT NULL,
  `province` tinytext NOT NULL,
  `street` tinytext NOT NULL,
  `positiont` tinytext NOT NULL,
  `unitn` tinytext NOT NULL,
  `unitl` tinytext NOT NULL,
  `incident` tinytext NOT NULL,
  `property` tinytext NOT NULL,
  `sandiamo` tinytext NOT NULL,
  `sbbg` tinytext NOT NULL,
  `stelxo` tinytext NOT NULL,
  `observations` mediumtext NOT NULL,
  `ip` tinytext NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

CREATE TABLE `user` (
  `id` tinyint(3) NOT NULL auto_increment,
  `user` tinytext NOT NULL,
  `pass` tinytext NOT NULL,
  `province` tinytext NOT NULL,
  `name` tinytext NOT NULL,
  `email` tinytext NOT NULL,
  `dni` tinytext NOT NULL,
  `login` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
Post Reply