Bytes - answers

select quiery problem

batman007 — Fri, 20 Nov 2009 14:32:48 GMT

Updated MySQL version - error:1064 You have an error in your SQL syntax

phobia1 — Fri, 20 Nov 2009 14:01:40 GMT

Hi once again.
We have just changed our ISP and things that worked fine now do not, Obviously its in the differences of MYSQL and PHP versions.
Have fixed most of the problems but this UPDATE routine has got me foxed.

Expand|Select|Wrap|Line Numbers

	
// update.php
 
require("./resources/globals.php") ;

require("./resources/common.php") ;

//echo $detail; 

//echo $passdata;

//echo $key;
 
$updateStmt = "Update Garant SET 

akey='$akey',

Ref='$Ref',

Broker='$Broker',

Brokerphone='$Brokerphone',

Brokeremail='$Brokeremail',

Comments='$Comments',

BGComments='$BGComments',

Price='$Price',

Location='$Location',

Town='$Town',

smallpic='$smallpic',

Pic1='$Pic1',

Pic2='$Pic2',

Pic3='$Pic3',

Pic4='$Pic4',

Portfolio='$Portfolio',

Size='$Size',

Condition='$Condition',

Plotsize='$Plotsize',

Roadaccess='$Roadaccess',

Distance='$Distance',

type='$type',

Electricity='$Electricity',

Water='$Water',

garage='$garage',

bedrooms='$bedrooms',

Kitchen='$Kitchen',

Status='$Status',

WC='$WC' WHERE akey = '$akey' ";
 
// Connect to the Database

if (!($link=mysql_connect($location,$userName,$password))) {

   DisplayErrMsg(sprintf("error connecting to host %s, by user %s",$location,$userName)) ;

  exit() ;

}
 
// Select the Database

if (!mysql_select_db($dbname, $link)) {

   DisplayErrMsg(sprintf("Error in selecting %s database", $dbname)) ;

   DisplayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link))) ;

   exit() ;

}
 
// Execute the Statement

if (!mysql_query($updateStmt, $link)){
 
// Execute the Statement/if (!mysql_query($updateStmt, $link)) {

  DisplayErrMsg(sprintf("Error in executing %s stmt", $updateStmt)) ;

 DisplayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link))) ;

  exit() ;

}

echo $updateStmt;

GenerateHTMLHeader("The entry was modified succesfully");
 
ReturnToDisplay( );
 
?>

Its giving error from the mysql_query section in the form of

Quote:

Error in executing Update Garant SET akey='120',Ref='D127',Broker='Dilyana',Brokerphone =' +359xxxxxxxxx +359xxxxxxxxx ',Brokeremail='dilyana@example.net',Comments='Trad itional Bulgarian house in a village 22 km south east of Veliko Tarnovo. This aristocratic property has been a shop (the front two premises faced to the main street of the village). The house consists of two attached parts. The ground floor of the first one is a large stone cellar (water inside), connected to the former shop with internal stairs. The first floor consists of 3 rooms in good condition. The second part of the house consists of 2 rooms on the ground floor and another 2 rooms on the first floor with beautiful view of Elena Mountain. Shower room attached to the second part. A barn finishes the building. There are 3 fireplaces in the house. Private garden 400 sq m with car space. Fruit trees and vines. Price: 21000 EUR',BGComments='?????�?????�?????????� ?�???�???�???????� ?????�?� ?? ???�?�?? 22 ???? ?????????�?�???�???? ???� ?�?�?�?????? ??????????????. ?� ???????�?�???�?? - ???????� ???� ?�???????�???????�?�??, ?????�?�?�?� ?� ?�???�?� ???�???�?�???? ?? ?????� ???????�?�?�?????? ?????? ???�?�?????�?�?� ???�???�?� ???� ???�?�???�??. ?????�?�, ???????�?????�?� ???� ???� 2 ?????????�?�???? ?�?�???�??: ???�?�?� ?? ?????�???�?????� ???????�???� ?????? ?�?????????? ???�???�?�???? ?? 3 ???�?�?? ?? ?????????? ?????�???? ???????�?????????� ???� ???�?????? ?�?�?�?�. ?�?�?????�?�?� ?�?�???� ???� ?????�?�?�?� ?????� 2 ???�?�?? ???� ?????????? ?�?�?�?� ?? ?????� ???�?�?? ???� ???�?????? ?�?�?�?�, ???�???????�?�?? ???� ???�?�?????????� ?????�???????� ???�?�?????� ?????? ?�?�?�?????????? ?�?�?�???�??. ?�???�?�???�???� ?�?�???? ?? ???�?�?????????�?????�???????� ???????�?????????� ?�?�???????????�?� ?????�?�?�?�, ???????�?? ?????� 2 ???�???????? ?? ???�?�???�?�. ?�???�???????� 400 ??2 ?? ???????�?? ?�?� ???�?????????�???� ???� ?????�?�. ???????????? ?? ?�???�??. ?�?�???�: 21000 ?�??????',Price='21000',Location='Veliko Tarnovo',Town='Veliko Tarnovo',smallpic='d127sm.jpg',Pic1='d127a.jpg',Pi c2='d127b.jpg',Pic3='d127c.jpg',Pic4='d127d.jpg',P ortfolio='existing',Size='200',Condition='for renovation',Plotsize='400',Roadaccess='good all year around',Distance='11 km to Zlataritsa',type='House',Electricity='yes',Water=' yes',garage='-',bedrooms='3',Kitchen='-',Status='for sale',WC='-' WHERE akey = '120' stmt
error:1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Condition='for renovation',Plotsize='400',Roadaccess='good all year around',Dist' at line 1

Could anyone push me in thr right direction please

select problem

pradeepjain — Fri, 20 Nov 2009 11:11:22 GMT

I have a table like this

Expand|Select|Wrap|Line Numbers

	+---------------------------+--------------------------+------+-----+---------+----------------+

| Field                     | Type                     | Null | Key | Default | Extra          |

+---------------------------+--------------------------+------+-----+---------+----------------+

| patient_id                | int(5) unsigned zerofill | NO   | PRI | NULL    | auto_increment | 

| Surname                   | varchar(100)             | YES  |     | NULL    |                | 

| Firstname                 | varchar(100)             | YES  |     | NULL    |                | 

| Gender                    | enum('Male','Female')    | YES  |     | NULL    |                | 

| Dob                       | date                     | YES  |     | NULL    |                | 

| Age                       | int(3)                   | YES  |     | NULL    |                | 

| Address                   | text                     | YES  |     | NULL    |                | 

| City                      | varchar(100)             | YES  |     | NULL    |                | 

| State                     | varchar(100)             | YES  |     | NULL    |                | 

| Pincode                   | int(10)                  | YES  |     | NULL    |                | 

| Address_landmark          | varchar(100)             | YES  |     | NULL    |                | 

| Landline                  | varchar(20)              | YES  |     | NULL    |                | 

| Mobile                    | varchar(20)              | YES  |     | NULL    |                | 

| Email                     | varchar(30)              | YES  |     | NULL    |                | 

| Smoker                    | enum('Yes','No')         | YES  |     | NULL    |                | 

| Alchohol                  | enum('Yes','No')         | YES  |     | NULL    |                | 

| Discover_diabetic         | text                     | YES  |     | NULL    |                | 

| Emergency_contact_name    | varchar(100)             | YES  |     | NULL    |                | 

| Emergency_telephone       | varchar(20)              | YES  |     | NULL    |                | 

| Physician_id              | int(10) unsigned         | NO   | MUL | NULL    |                | 

| Physician_name            | varchar(100)             | YES  |     | NULL    |                | 

| Physician_phone           | varchar(20)              | YES  |     | NULL    |                | 

| Physician_Mobile          | varchar(20)              | YES  |     | NULL    |                | 

| Physician_after_office    | varchar(20)              | YES  |     | NULL    |                | 

| consultation              | enum('Yes','No')         | YES  |     | NULL    |                | 

| Diabetologist_name        | varchar(100)             | YES  |     | NULL    |                | 

| Diabetologist_number      | varchar(20)              | YES  |     | NULL    |                | 

| Chiropodist_name          | varchar(100)             | YES  |     | NULL    |                | 

| Chiropodist_number        | varchar(20)              | YES  |     | NULL    |                | 

| Hospital_name             | varchar(100)             | YES  |     | NULL    |                | 

| Hospital_number           | varchar(20)              | YES  |     | NULL    |                | 

| Diabetes_nurse_name       | varchar(100)             | YES  |     | NULL    |                | 

| Diabetes_nurse_number     | varchar(20)              | YES  |     | NULL    |                | 

| Type_of_diabetes          | varchar(100)             | YES  |     | NULL    |                | 

| Date_of_diagnosis         | date                     | YES  |     | NULL    |                | 

| Symptoms_at_diagnosis     | text                     | YES  |     | NULL    |                | 

| Weight_at_diagnosis       | int(3)                   | YES  |     | NULL    |                | 

| Past_family_history       | text                     | YES  |     | NULL    |                | 

| Past_medical_history      | text                     | YES  |     | NULL    |                | 

| Allergies                 | text                     | YES  |     | NULL    |                | 

| Doctors_observation       | text                     | YES  |     | NULL    |                | 

| Alternate_medicine        | enum('Yes','No')         | YES  |     | NULL    |                | 

| Alternate_medicine_reason | text                     | YES  |     | NULL    |                | 

| addedon                   | varchar(50)              | NO   |     | NULL    |                | 

| addedip                   | varchar(50)              | NO   |     | NULL    |                | 

| addedby                   | varchar(100)             | NO   |     | NULL    |                | 

| updatedon                 | varchar(50)              | NO   |     | NULL    |                | 

| updatedip                 | varchar(50)              | NO   |     | NULL    |                | 

| updatedby                 | varchar(100)             | NO   |     | NULL    |                | 

| userdetails               | text                     | YES  |     | NULL    |                | 

+---------------------------+--------------------------+------+-----+---------+----------------+

and i have values in table like

Expand|Select|Wrap|Line Numbers

	
 select * from viioforms_form1;

+------------+---------+-----------+--------+------------+------+-----------------------------+-------------+-------------+---------+------------------+------------+--------------+--------------------------+--------+----------+-------------------+------------------------+---------------------+--------------+----------------+-----------------+------------------+------------------------+--------------+--------------------+----------------------+------------------+--------------------+---------------+-----------------+---------------------+-----------------------+------------------+-------------------+-----------------------+---------------------+---------------------+----------------------+-----------+---------------------+--------------------+---------------------------+--------------+-----------------+---------+-----------+-----------+-----------+-------------------------------------------------------------------------------------------------------------------+

| patient_id | Surname | Firstname | Gender | Dob        | Age  | Address                     | City        | State       | Pincode | Address_landmark | Landline   | Mobile       | Email                    | Smoker | Alchohol | Discover_diabetic | Emergency_contact_name | Emergency_telephone | Physician_id | Physician_name | Physician_phone | Physician_Mobile | Physician_after_office | consultation | Diabetologist_name | Diabetologist_number | Chiropodist_name | Chiropodist_number | Hospital_name | Hospital_number | Diabetes_nurse_name | Diabetes_nurse_number | Type_of_diabetes | Date_of_diagnosis | Symptoms_at_diagnosis | Weight_at_diagnosis | Past_family_history | Past_medical_history | Allergies | Doctors_observation | Alternate_medicine | Alternate_medicine_reason | addedon      | addedip         | addedby | updatedon | updatedip | updatedby | userdetails                                                                                                       |

+------------+---------+-----------+--------+------------+------+-----------------------------+-------------+-------------+---------+------------------+------------+--------------+--------------------------+--------+----------+-------------------+------------------------+---------------------+--------------+----------------+-----------------+------------------+------------------------+--------------+--------------------+----------------------+------------------+--------------------+---------------+-----------------+---------------------+-----------------------+------------------+-------------------+-----------------------+---------------------+---------------------+----------------------+-----------+---------------------+--------------------+---------------------------+--------------+-----------------+---------+-----------+-----------+-----------+-------------------------------------------------------------------------------------------------------------------+

|      00001 |  m      |  pradeep  |        | 1984-08-19 |   25 |  #287,2nd block,2nd cross,  |  bangalore  |  karnataka  |  560085 |  pillar          |  26792042  |  9980572765  |  gpradeepjain@gmail.com  |        |          |  yesterday        |  pradeep               |  26792042           |            1 |  ashok         |  26792042       |  9980572765      |  9980572765            |              |  naveen1           |  9980572761          |  naveen2         |  9980572762        |  naveen3      |  9980572763     |  naveen4            |  9980572764           |  not known       | 2009-09-09        |  unknown              |                  78 |  none               |  none1               |  nope     |  not yet done       |                    |  no reason                |  1258714681  |  192.168.2.112  |  admin  |           |           |           |  Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.3) Gecko/20090909 Fedora/3.5.3-1.fc11 Firefox/3.5.3 FirePHP/0.3 | 

|      00002 |  m      |  pradeep  |        | 1984-08-19 |   25 |  #287,2nd block,2nd cross,  |  bangalore  |  karnataka  |  560085 |  pillar          |  26792042  |  9980572765  |  gpradeepjain@gmail.com  |        |          |  yesterday        |  pradeep               |  26792042           |            1 |  ashok         |  26792042       |  9980572765      |  9980572765            |              |  naveen1           |  9980572761          |  naveen2         |  9980572762        |  naveen3      |  9980572763     |  naveen4            |  9980572764           |  not known       | 2009-09-09        |  unknown              |                  78 |  none               |  none1               |  nope     |  not yet done       |                    |  no reason                |  1258714684  |  192.168.2.112  |  admin  |           |           |           |  Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.3) Gecko/20090909 Fedora/3.5.3-1.fc11 Firefox/3.5.3 FirePHP/0.3 | 

|      00003 |  m      |  pradeep  |        | 1984-08-19 |   25 |  #287,2nd block,2nd cross,  |  bangalore  |  karnataka  |  560085 |  pillar          |  26792042  |  9980572765  |  gpradeepjain@gmail.com  |        |          |  yesterday        |  pradeep               |  26792042           |            1 |  ashok         |  26792042       |  9980572765      |  9980572765            |              |  naveen1           |  9980572761          |  naveen2         |  9980572762        |  naveen3      |  9980572763     |  naveen4            |  9980572764           |  not known       | 2009-09-09        |  unknown              |                  78 |  none               |  none1               |  nope     |  not yet done       |                    |  no reason                |  1258715003  |  192.168.2.112  |  admin  |           |           |           |  Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.3) Gecko/20090909 Fedora/3.5.3-1.fc11 Firefox/3.5.3 FirePHP/0.3 | 

+------------+---------+-----------+--------+------------+------+-----------------------------+-------------+-------------+---------+------------------+------------+--------------+--------------------------+--------+----------+-------------------+------------------------+---------------------+--------------+----------------+-----------------+------------------+------------------------+--------------+--------------------+----------------------+------------------+--------------------+---------------+-----------------+---------------------+-----------------------+------------------+-------------------+-----------------------+---------------------+---------------------+----------------------+-----------+---------------------+--------------------+---------------------------+--------------+-----------------+---------+-----------+-----------+-----------+-------------------------------------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

but when i give query like

Expand|Select|Wrap|Line Numbers

	select * from viioforms_form1 where Surname='m' and Firstname='pradeep';

Empty set (0.00 sec)

it should give th result . but its not ....i am not knowing the reason.

datatype with data like 2008-09

kkshansid — Wed, 18 Nov 2009 11:43:07 GMT

what datatype should i take for field financial year with data like 2008-09?
kindly suggest.................

what is your idea about this database design?

ali1 — Tue, 17 Nov 2009 16:53:35 GMT

Hi all;

I appreciate that you write your idea for this db,is it correct?

magazine db design:
-one article can belong to many categories.
-one article can have null or many photos.
-one article can have null or many tags.

tb_categories|cat_id(pk) , catname , parent_id

tb_articles|article_id(pk) , title , subtitle , textbody , source_id , date , remark

tb_sources|source_id(pk) , sourcename

tb_phptos|photo_id(pk) , photofilename

tb_tags|tag_id(pk) , tagname

tb_articles_photo|article_id(fk) , photo_id(fk)

tb_articles_tags|article_id(fk) , tag_id(fk)

tb_articles_categories|article_id(fk) , cat_id(fk)

thanks

How to Create a User Registration Database

naharol — Tue, 17 Nov 2009 12:23:12 GMT

hi guys,

I would like to have the function to enable my visitors to register with my web site, like they do with my forums and maybe receive an email newsletter.

I see all over the net the small simple to use "enter your email to register" forms. I would really like to know how to go about producing one of those for my site, and how to go about creating and maintaining the database of users.

I have searched the net, obviously not using the correct search terms, but cannot seem to find much help on this at all.

Any help would be very much appreciated as usual.

Best regards

Naharol.

Problem using 'OUTFILE' to produce a backup of table

chazzy69 — Tue, 17 Nov 2009 04:30:32 GMT

Im currently using a php script to access a database and use the following lines to produce a backup of a table-

Expand|Select|Wrap|Line Numbers

	
$query = "SELECT * INTO OUTFILE 'test.sql' FROM `default_en_listingsdbelements`";
 
$result = mysql_query($query);

Which seems to execute fine and produces no errors except it doesn't actually produce any file. I figured it would produce the file in the same directory as the script or if not in the root directory but neither contains the file.

I then tested the same line in phpmyadmin/mysql which produced user does not have permission to create file.

So im a little unsure as what to do from here, do i need to change permission for the user and if so what access do i need to allow or am i using the 'OUTFILE' function incorrectly?

Any help would be greatly appreciated, note i think the username im using currently has all access permissions-

user_name (Privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE ROUTINE)

error Every derived table must have its own alias

kkshansid — Sun, 15 Nov 2009 17:40:46 GMT

Expand|Select|Wrap|Line Numbers

	query 1=SELECT institute.state, result. * 

FROM institute

INNER JOIN result ON centrecode = original_c

then i want to execute query on table 1

Expand|Select|Wrap|Line Numbers

	SELECT state, sum( IF( PASS_FAIL = 'P', 1, 0 ) ) AS tp from query1

group BY state

but when i execute it gives error

Expand|Select|Wrap|Line Numbers

	Error

SQL query:  
 
SELECT state, sum( IF( PASS_FAIL = 'P', 1, 0 ) ) AS tp

FROM (
 
SELECT institute.state, result. * 

FROM institute

INNER JOIN result ON centrecode = original_c

)

group BY state

LIMIT 0 , 30 
 
MySQL said:  
 
#1248 - Every derived table must have its own alias

kindly help me to correct it

merge two queries into one

kkshansid — Fri, 13 Nov 2009 06:30:31 GMT

State wise count of excellent (max pass count centres)
query1->

Expand|Select|Wrap|Line Numbers

	Select  code,sum( IF(PASS_FAIL='P',1,0)) AS Cp from table group by code

I made table5 from above query then i executed query2 to get desired data
Query2->

Expand|Select|Wrap|Line Numbers

	SELECT Sheet1.state, code,Sheet1.address,stdcnt

FROM Table5 INNER JOIN Sheet1 ON Table5.code = Sheet1.centrecode

order by state,stdcnt  desc;

kindly help me to merge these two queries into one

I have100 databases with tbl, need to get count of enrolled records from tbl using pr

saranyagunasekaran — Fri, 13 Nov 2009 01:37:17 GMT

I have1000 databases with table called tbl1 which has got enrolled as column name, need to get count of enrolled records from tbl1 for all 1000 databases using procedures.............or it would be fine...if trigger is being set as soon as databases got created wuth tbl tbl1 and column enrolled to gine count.

get me the reply asap..............

Select unused records in a listbox

CoreyReynolds — Thu, 12 Nov 2009 13:35:07 GMT

Hello all!

Thanks everyone for the great help you've given me so far on this forum - I have another intermediate question.

I currently have a form where a user chooses a piece of equipment, followed by a subform where they can add records to it which are different parts that piece of equipment can have, which is working good! The user just clicks a drop down, selects the compartment they want to add and they

My tables go tblEquipment (equipmentID and equipmentName) - tblCompartment (equipmentID and compartmentCode) -tblCompartmentList (compartmentCode and compartmentDescription)

My current rowsource for the dropdown box in my sub form is "SELECT tblCompartmentList.compartmentCode, tblCompartmentList.compartmentName
FROM tblCompartmentList;" and its Control Source is set to dump their selection into the compartment table.

My Question:
I only want compartments that have NOT yet been added to that specific equipment to show up in that drop down box, so that if the user say adds a transmission, then adds another record, transmission will not be an available selection.

Thanks in advance. Hopefully I have provided enough information but please let me know if you need anything else!

Web site database design

naharol — Thu, 12 Nov 2009 12:36:46 GMT

Hi,
I want to create a database for my web site , m using wampserver.. but i dont have any idea how a web site database will be . can any body explain me how a web site database will be ?.
thanks

Comparing Two Tables and Deleting duplicate

nhatto — Wed, 11 Nov 2009 20:29:30 GMT

Hello

I have Two Tables ( Table A and Table B)

Table A has one field : email
Table B has three Fields : First name, Last name , email

I want to check if the email in Table B is found in Table A, And if it is , then I want to delete the row ( First name, Last name , email ) from Table B that has that email.

any help is really appreciated and thank you in advance!

About a SELECT command to execute in my MysQL Database

Carcinosi — Wed, 11 Nov 2009 19:21:24 GMT

Hello,

I have a MySQL 5.0 Database used for management of my service, with 13 tables.

The two tables most important now are "customers" and "cust_packages".

The table "customers" has one column "cust_email". This column has e-mails of customers of the my service.

The table named "cust_packages" has one column "cpack_canceldate". This column has the date of canceled accounts, for example '2008-04-03'. If the account is not canceled, so this column has '0000-00-00'.

Both tables "customers" and "cust_packages" have "cust_custnr" and "cpack_usernr" respectively with interconnected values.

These columns have an identification number exclusive for each customer.

For example, I have one customer with the number '110002' in the column "cust_custnr" of the table "customers". The same number is in the column "cpack_usernr" of the table "cust_packages".

I want to select all e-mails of customers with canceled accounts.

But there is a problem. The same customer may have two accounts...the same number may appear two or more times in the colum "cpack_usernr" of the table "cust_packages". For example, the customer '110002' in the past cancelled his account, but recently he opened another account. Because this, the same number 110002 appear two times in the column "cpack_usernr" of the table "cust_packages". I need only exclusive customers with canceled accounts.

So, I want to select all e-mails of customers with canceled accounts AND ONLY for customers which appear ONE TIME in the colum "cpack_usernr" of the table "cust_packages".

Thanks.

How do I show records not in another table in a subform?

CoreyReynolds — Wed, 11 Nov 2009 14:34:42 GMT

Hello,

I am currently about to give up after scouring the internet for how to do this, it's probably a relatively novice answer, but I am a relatively novice user.

I have three tables

Expand|Select|Wrap|Line Numbers

	tblEquipment: (P) equipmentID, equipmentDescription

tblCompartment: compartmentCode, equipmentID, (P)compartmentAutoID

tblCompartmentList: (P)compartmentCode, compartmentName

The equipment table is joined one to many to tblCompartment, and tblCompartmentList is joined one to many on tblCompartment.

I have a Form where users choose from a drop box a piece of equipment. Below that I have a sub form. In that subform I want to display each compartmentCode and compartmentName that has not been entered for that piece of equipment into tblCompartmentID.

Heres my code that doesn't seem to exist for that subform:

Expand|Select|Wrap|Line Numbers

	
SELECT 

    tblCompartmentList.compartmentCode, 

    tblCompartmentList.compartmentName, 

    tblCompartment.equipmentID

FROM 

    tblCompartmentList 

LEFT JOIN tblCompartment 

    ON tblCompartmentList.compartmentCode = tblCompartment.compartmentCode

WHERE 

    (((tblCompartment.compartmentCode) Is Null));

What am I doing wrong? Is there a better way?