Categories > TinyButStrong general >

What's wrong with this MySQL

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Shawn
Date: 2005-03-25
Time: 16:49

What's wrong with this MySQL

I'm trying to pull data out of the MySQL database where the username matches.

SELECT ad.upload
  , ad.value AS 'Address'
  , cy.value AS 'City'
  , st.value AS 'State'
  , ap.value AS 'Asking Price'
  , ty.value AS 'Type'
  , ph.value AS 'Contact Phone'
  , bdrms.value AS 'Bedrooms'
  , bath.value AS 'Bathrooms'
  , sqf.value AS 'Square Footage'
  , usn.value AS 'Username'
FROM ezu_uploadinfos AS ad
LEFT JOIN ezu_uploadinfos AS cy ON (ad.upload=cy.upload) AND (cy.name='City')
LEFT JOIN ezu_uploadinfos AS st ON (ad.upload=st.upload) AND (st.name='State')
LEFT JOIN ezu_uploadinfos AS ap ON (ad.upload=ap.upload) AND (ap.name='Asking Price')
LEFT JOIN ezu_uploadinfos AS ty ON (ad.upload=ty.upload) AND (ty.name='Type')
LEFT JOIN ezu_uploadinfos AS ph ON (ad.upload=ph.upload) AND (ph.name='Contact Phone')
LEFT JOIN ezu_uploadinfos AS bdrms ON (ad.upload=bdrms.upload) AND (bdrms.name='Bedrooms')
LEFT JOIN ezu_uploadinfos AS bath ON (ad.upload=bath.upload) AND (bath.name='Bedrooms')
LEFT JOIN ezu_uploadinfos AS sqf ON (ad.upload=sqf.upload) AND (sqf.name='Square Footage')
LEFT JOIN ezu_uploadinfos AS usn ON (ad.upload=usn.upload) AND (usn.name='Username')
WHERE (ad.name='Address') AND (usn.name='Username')


What is happening, no matter what I login as, I'm pulling data that shouldn't be pulled.

I basically want to say, WHERE username='$username'

But it's not happeing.
By: Anonymous
Date: 2005-03-25
Time: 20:07

Re: What's wrong with this MySQL

Tried this too with no luck.

SELECT usn.upload
  , ad.value AS 'Address'
  , cy.value AS 'City'
  , st.value AS 'State'
  , ap.value AS 'Asking Price'
  , ty.value AS 'Type'
  , ph.value AS 'Contact Phone'
  , bdrms.value AS 'Bedrooms'
  , bath.value AS 'Bathrooms'
  , sqf.value AS 'Square Footage'
  , usn.value AS 'Username'
FROM ezu_uploadinfos AS usn
LEFT JOIN ezu_uploadinfos AS cy ON (usn.upload=cy.upload) AND (cy.name='City')
LEFT JOIN ezu_uploadinfos AS st ON (usn.upload=st.upload) AND (st.name='State')
LEFT JOIN ezu_uploadinfos AS ap ON (usn.upload=ap.upload) AND (ap.name='Asking Price')
LEFT JOIN ezu_uploadinfos AS ty ON (usn.upload=ty.upload) AND (ty.name='Type')
LEFT JOIN ezu_uploadinfos AS ph ON (usn.upload=ph.upload) AND (ph.name='Contact Phone')
LEFT JOIN ezu_uploadinfos AS bdrms ON (usn.upload=bdrms.upload) AND (bdrms.name='Bedrooms')
LEFT JOIN ezu_uploadinfos AS bath ON (usn.upload=bath.upload) AND (bath.name='Bedrooms')
LEFT JOIN ezu_uploadinfos AS ad ON (usn.upload=ad.upload) AND (ad.name='Address')
LEFT JOIN ezu_uploadinfos AS sqf ON (usn.upload=sqf.upload) AND (sqf.name='Square Footage')
WHERE usn.upload='testing'
By: Skrol29
Date: 2005-03-25
Time: 20:46

Re: What's wrong with this MySQL

It probably should be
WHERE (usn.value='TheUserILookFor')

Just a remark: in your SQL, user info are linked to the Address item (record). This assumes that all user info has an Address item. But I guess the item that all user must have is UserName. So user info should be linked to the UserName item. (swap 'ad' and 'usn')
By: Anonymous
Date: 2005-03-25
Time: 21:14

Re: What's wrong with this MySQL

Thanks. Let me try that.
By: Shawn
Date: 2005-03-25
Time: 21:31

Re: What's wrong with this MySQL

Skrol,

Thanks buddy.  I think that did it, now I can move forward, until the next problem with my novice skills.  Smile.
By: Shawn
Date: 2005-03-26
Time: 01:01

Re: What's wrong with this MySQL

Skrol,

Worked perfectly.  A few more projects with TBS and I might become an intermediate user.  Smile.

TBS is by far the easiest and IMHO the best template engine.
By: Shawn
Date: 2005-03-26
Time: 02:51

Re: What's wrong with this MySQL

Skrol,

How would I join a second table to this table.

For instance I have a table where images are stored.  The table is ezu_uploads and I have the following columns id, upload, name, type, size.  The upload column is the same in each table, both the ezu_uploadinfos and the ezu_files table.

How would I join the two together?  Thanks.
By: Shawn
Date: 2005-03-26
Time: 05:50

Re: What's wrong with this MySQL

Still can't get this.  Here's my image table that I need to also join and get data from.

table name = ezu_files
colums = id, upload, name

upload is common between the two tables.  I need to get the name of the file.


SELECT usn.upload
  , ad.value AS 'Address'
  , cy.value AS 'City'
  , st.value AS 'State'
  , ap.value AS 'Asking Price'
  , ty.value AS 'Type'
  , ph.value AS 'Contact Phone'
  , bdrms.value AS 'Bedrooms'
  , bath.value AS 'Bathrooms'
  , sqf.value AS 'Square Footage'
  , fn.value AS 'First Name'
  , ln.value AS 'Last Name'
FROM ezu_uploadinfos AS ad
LEFT JOIN ezu_uploadinfos AS cy ON (usn.upload=cy.upload) AND (cy.name='City')
LEFT JOIN ezu_uploadinfos AS st ON (usn.upload=st.upload) AND (st.name='State')
LEFT JOIN ezu_uploadinfos AS ap ON (usn.upload=ap.upload) AND (ap.name='Asking Price')
LEFT JOIN ezu_uploadinfos AS ty ON (usn.upload=ty.upload) AND (ty.name='Type')
LEFT JOIN ezu_uploadinfos AS ph ON (usn.upload=ph.upload) AND (ph.name='Contact Phone')
LEFT JOIN ezu_uploadinfos AS bdrms ON (usn.upload=bdrms.upload) AND (bdrms.name='Bedrooms')
LEFT JOIN ezu_uploadinfos AS bath ON (usn.upload=bath.upload) AND (bath.name='Bedrooms')
LEFT JOIN ezu_uploadinfos AS sqf ON (usn.upload=sqf.upload) AND (sqf.name='Square Footage')
LEFT JOIN ezu_uploadinfos AS usn ON (usn.upload=ad.upload) AND (ad.name='Address')
LEFT JOIN ezu_uploadinfos AS fn ON (usn.upload=fn.upload) AND (fn.name='First Name')
LEFT JOIN ezu_uploadinfos AS ln ON (usn.upload=ln.upload) AND (ln.name='Last Name')
LEFT JOIN ezu_files AS uf ON (usn.upload=uf.upload)
LEFT JOIN ezu_files AS ff ON (usn.upload=ff.name)
WHERE (usn.value='sb@loginemailaddress')


Thanks.
By: Skrol29
Date: 2005-03-27
Time: 14:33

Re: What's wrong with this MySQL

In which column is stored the file name?
Don't you have any "value" field in the "ezu_files" table?
By: Anonymous
Date: 2005-03-27
Time: 15:36

Re: What's wrong with this MySQL

In the ezu_files table I have the following columns
id
upload
name
type
size

name is where the name of the file is stored.  the column upload is common between table ezu_files and ezu_uploadinfos.  In the upload column, for instance if two images are uploaded, both tables will place a number for each image uploaded.
By: Shawn
Date: 2005-03-27
Time: 15:40

Re: What's wrong with this MySQL

I tried the following code, but it doesn't display the images, and shows NULL.  Also doesn't appear that all images would show.

Still trying to get it. 


SELECT usn.upload
  , ad.value AS 'Address'
  , cy.value AS 'City'
  , st.value AS 'State'
  , ap.value AS 'Asking Price'
  , ty.value AS 'Type'
  , ph.value AS 'Contact Phone'
  , bdrms.value AS 'Bedrooms'
  , bath.value AS 'Bathrooms'
  , sqf.value AS 'Square Footage'
  , fn.value AS 'First Name'
  , ln.value AS 'Last Name'
  , nm.name AS 'Image'
FROM ezu_uploadinfos AS ad
LEFT JOIN ezu_uploadinfos AS cy ON (usn.upload=cy.upload) AND (cy.name='City')
LEFT JOIN ezu_uploadinfos AS st ON (usn.upload=st.upload) AND (st.name='State')
LEFT JOIN ezu_uploadinfos AS ap ON (usn.upload=ap.upload) AND (ap.name='Asking Price')
LEFT JOIN ezu_uploadinfos AS ty ON (usn.upload=ty.upload) AND (ty.name='Type')
LEFT JOIN ezu_uploadinfos AS ph ON (usn.upload=ph.upload) AND (ph.name='Contact Phone')
LEFT JOIN ezu_uploadinfos AS bdrms ON (usn.upload=bdrms.upload) AND (bdrms.name='Bedrooms')
LEFT JOIN ezu_uploadinfos AS bath ON (usn.upload=bath.upload) AND (bath.name='Bedrooms')
LEFT JOIN ezu_uploadinfos AS sqf ON (usn.upload=sqf.upload) AND (sqf.name='Square Footage')
LEFT JOIN ezu_uploadinfos AS usn ON (usn.upload=ad.upload) AND (ad.name='Address')
LEFT JOIN ezu_uploadinfos AS fn ON (usn.upload=fn.upload) AND (fn.name='First Name')
LEFT JOIN ezu_uploadinfos AS ln ON (usn.upload=ln.upload) AND (ln.name='Last Name')
LEFT JOIN ezu_files AS nm ON (usn.upload=nm.name) AND (nm.name='Image')
WHERE (usn.value='sb@cableone.net')
By: Shawn
Date: 2005-03-27
Time: 15:44

Re: What's wrong with this MySQL

Just tried the following code and it gets close, but I would like to be able to do all this in one state:

select ezu_uploadinfos.*, ezu_files.* from ezu_uploadinfos, ezu_files
where ezu_uploadinfos.upload=ezu_files.upload
and ezu_uploadinfos.value = 'email@email.com'
By: Skrol29
Date: 2005-03-27
Time: 16:50

Re: What's wrong with this MySQL

Just change
LEFT JOIN ezu_files AS nm ON (usn.upload=nm.name) AND (nm.name='Image')
into
LEFT JOIN ezu_files AS nm ON (usn.upload=nm.upload)

nm.name='Image'
is not necessary since filed 'Name" is never equal to "Image", but stores the image's file name.