Welcome Guest, Not a member yet? Register   Sign In
mysql select
#1

[eluser]georgerobbo[/eluser]
Heyy,

I have a table which I am querying. I want to know what is the best method to find results from the following name, type, permalink and tags.

Quote:ID INT(11)
Name Varchar(255)
Type Varchar(255)
Permalink Varchar(255)
Tags Varchar(255)
Address Varchar(255)
Town Varchar(255)
Postcode Varchar(255)
Phone No. Varchar(255)
#2

[eluser]jedd[/eluser]
[quote author="georgerobbo" date="1254884293"]
I want to know what is the best method of using (mysql select where).
[/quote]

Eh wot?

What's your current select query looking like?

Quote:Type Varchar(255)
Tags Varchar(255)
Postcode Varchar(255)

Are you going to have 255*255 types?

A 255-character postcode? Holy moly.

And fix up your tags - it will make things much easier.
#3

[eluser]georgerobbo[/eluser]
Code:
$query = "SELECT * FROM user WHERE name like \"%$keyphrase%\" OR tag like \"%$keyphrase%\" OR permalink like \"%$keyphrase%\" OR type like \"%$keyphrase%\" ";
    
    $numresults = mysql_query($query);
    $numrows = mysql_num_rows($numresults);
#4

[eluser]jedd[/eluser]
[quote author="georgerobbo" date="1254885006"]
Those are not the true lengths I'm using. Just what I have written down for simplicity.
[/quote]

I do not understand how misrepresenting a schema, especially when asking for help about that schema, makes things simpler.

Your code looks pretty easy to translate into CI - if that's the question you're actually asking here (I can't work it out).

I create my own queries in preference to using CI's AR - you haven't said if your desire is to move to AR calls or not.

Very roughly - something that looks like this:
Code:
$query = $this->db->query ("SELECT *
                FROM user
                WHERE name like \"%". $this->db->escape($keyphrase). "%\"
                OR permalink like \"%". $this->db->escape($keyphrase)."%\"    ... etc
                ");
$numresults = $query->num_rows();

As I say, though, you need to fix up your tagging system - have a table for tags, and another table for connecting the tags table to this user table.
#5

[eluser]georgerobbo[/eluser]
Well I'm looking for help on how to query the database. I have created a new table for tags. What is the best way to query and match the results from both tables.
#6

[eluser]jedd[/eluser]
[quote author="georgerobbo" date="1254921761"]
Well I'm looking for help on how to query the database. I have created a new table for tags. What is the best way to query and match the results from both tables.
[/quote]

I've got a dreadful cold, and I'm feeling pretty danged poorly. Which may explain any sarcasm present in the following.

Hey - here's a clue! If you're asking questions about schemas and sql queries, you should provide your schema and whatever sql queries you've already come up with. If you make changes to your schema, you should describe - in detail - what they are, rather than allude to them in vague terms (unless you seek very vague answers - such as 'use the SELECT, Luke').

The fact that you have added 'a new table' to handle tags is both concerning and confusing, and suggests you have misunderstood. I can't explain what you're doing wrong until I see what you're doing, however.

Finally, give a hint about the nature (format/content) of the output you're seeking.
#7

[eluser]georgerobbo[/eluser]
[quote author="jedd" date="1254924990"][quote author="georgerobbo" date="1254921761"]
Well I'm looking for help on how to query the database. I have created a new table for tags. What is the best way to query and match the results from both tables.
[/quote]

I've got a dreadful cold, and I'm feeling pretty danged poorly. Which may explain any sarcasm present in the following.

Hey - here's a clue! If you're asking questions about schemas and sql queries, you should provide your schema and whatever sql queries you've already come up with. If you make changes to your schema, you should describe - in detail - what they are, rather than allude to them in vague terms (unless you seek very vague answers - such as 'use the SELECT, Luke').

The fact that you have added 'a new table' to handle tags is both concerning and confusing, and suggests you have misunderstood. I can't explain what you're doing wrong until I see what you're doing, however.

Finally, give a hint about the nature (format/content) of the output you're seeking.[/quote]


Apologies. The structure of my table, named user is as follows.


Quote:ID(int)(11)
name(varchar)(25)
type(varchar)(50)
tag(varchar) (200)
permalink(varchar)(100)
description(varchar)(255)
address_ln1(varchar)(50)
address_ln2(varchar)(50)
town(varchar)(25)
postcode(varchar)(8)
phone(varchar)(12)

My code to query the database is as the following.

Code:
/* Select MySQL Database && Query && Return */
    
    echo database_conf();
    
    $query = "SELECT * FROM user WHERE name like \"%$keyphrase%\" OR tag like \"%$keyphrase%\" OR permalink like \"%$keyphrase%\" OR type like \"%$keyphrase%\" ";
    
    $numresults = mysql_query($query);
    $numrows = mysql_num_rows($numresults);

I am not developing this project in CodeIgniter. I am looking for advice on how to better structure my database and my query (especially the tag feature).
#8

[eluser]jedd[/eluser]
[quote author="georgerobbo" date="1255041993"]
I am not developing this project in CodeIgniter.
[/quote]

Why are you posting on the CI forums, then?

[quote author="georgerobbo" date="1254921761"]
I have created a new table for tags.

...

Apologies. The structure of my table, named user is as follows.

Code:
ID(int)(11)
name(varchar)(25)
type(varchar)(50)
tag(varchar) (200)
permalink(varchar)(100)
description(varchar)(255)
address_ln1(varchar)(50)
address_ln2(varchar)(50)
town(varchar)(25)
postcode(varchar)(8)
phone(varchar)(12)
[/quote]

It looks like you have not, in fact, created a tag table.

To answer your question - how to write better queries - relies upon you having a better schema.

This in turn relies on you having a table to hold tags, and a table to manage the relationship between users and tags.

The former would look like this:
Code:
CREATE TABLE tag (
         id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
         name      CHAR(100) UNIQUE NOT NULL,
         PRIMARY KEY(id),
         INDEX (name),
         );

The latter would look like this:
Code:
CREATE TABLE tag_user (
         id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
         tag_id       INT UNSIGNED NOT NULL,
         user_id      INT UNSIGNED NOT NULL,
         PRIMARY KEY(id),
         INDEX (tag_id),
         INDEX (user_id),
         );

Needless to say, you remove the tag column from your user table.




Theme © iAndrew 2016 - Forum software by © MyBB