[finding duplicate records using tidbits]

If you want to find all users with 'priestthe' in their email, you could do this;

 

SELECT * FROM table WHERE email  LIKE '%priestthe%';

 

But ofcourse that only works if you already know that you're looking for 'priestthe'.

Databases generally have fuzzy string functions which you can use to calculate how different two strings are. Look at SOUNDEX and LEVENSHTEIN. They're not fast but speed isn't an issue here I think.

 

For example, in PostgreSQL, this:

 

SELECT levenshtein('acct 1 - priesttheugk4729@' , 'acct 2 - priestthe4729@');

 

Returns '4', indicating that the two strings only differ by four characters, which is odd for two strings that are more than 20 characters long.

[Trying to use URL from MySQL in php]

ps: Use single-quotes in PHP, it mkase your HTML more readable and reduces bugs with the escaping slashes:

 

echo '<a class="fancy_button" href="'.$app_download.'" target="_blank"><span style="background-color: #004080;">Download</span></a>';

[LEFT JOIN with "NOT LIKE"]

Connection errors usually indicate timeouts or resultsets that are too large to handle.

 

Using LIKE on a string that starts with an % sign is painfully slow. If the query takes too long to complete, the server or the connection might timeout.

 

How long does the query run before you get this error?

[Simple query help]

Yes that works, but don't forget to add errorchecking and escape the $map value. Otherwise you're completely exposed to SQL-Injection.

[pconnect error with Adobe Dreamweaver cs6]

Also note thast 'localhost' and '127.0.0.1' point to the same thing, but MySQL's accessrights treat them differently.

 

Is the MySQL server running on the same server (very possible, but most hosting companies have dedicated database servers  for that)

[Referential Integrity for restricted areas?]

 

I'm just wondering, is it better to hard code it or reference it from a table in the database according to Referential Integrity?

 

A number works for your application, but it means nothing to you as a human being, so a name is required. You can use a lookup table that links the number to an integer, that would restrict the possible values to only those you have defined. Performancewise; you're never going to use that lookup table anywhere other than in the bit where you display the rights that can be assigned, probably in some admin interface.

 

You could store the names in the application, but then your application must be in sync with the database at all times, which sounds easy enough but it will bite you when you do silly things like upgrade your website where either the database or the application will be upgraded first.

 

However, I'd make a few changes in your setup; right now you have an optimistic setup; nobody is restricted unless there are restrictions defined for them. Normally you'd want the opposite; everybody is completely restricted unless there are records that give them access to particular areas.

 

If you use a role based system you eliminate the need for the "is restricted" boolean and save yourself a lot of admin work.

[DISTINCT 2 Table]

What do you mean by DISTINCT, because group-by does not do anything like DISTINCT, and you can't SUM on a DISTINCT without losing data.

 

 

SELECT i.whatever item fields, SUM(q.quantity) AS quantity
FROM whatever table is the master list of items AS i
JOIN whatever table has the quantity AS q ON matching item
GROUP BY i.identifier column

 

Remember  that "i.whatever itemfields" can only contain what's mentioned in the group-by part, otherwise you will get unreliable data.

Make sure to configure your MySQL to "TRADITIONAL", that prevent's MySQL from allowing you to make silly mistakes that brake your

data.

[Where Exists Help]

The quotes around the fieldnames are wrong, but not a syntax error.

 

The error is in the WHERE EXISTS that just appears in the middle of nowhere:

 

INSERT INTO fieldtally(pipeno,wthick,djointno,heatno1)VALUES('$pipeno','$wthick','$djointno','$heatno1'); WHERE EXIST, UPDATE fieldtally set 'pipeno'=$pipeno,'wthick'=$wthick,'djointno'=$djointno,'heatno1'=$heatno1);

 

I think you've been writing a query using English grammer rather than SQL grammer.

 

What are you trying to do with the query?

[How to join 3 queries?[]

Can you provide a small sample of data and an example output that you'd like to get from that data? 

[SIMILAR TO issue]

Sounds like you can get away with a simple LIKE statement:

 

WHERE title LIKE '%' || your_string_here || '%'

 

The % signs are wildcards and the || signs append the who thing together to become '%Robocop%'

 

Note however that LIKE statements that start with a wildcard can be very slow because the database cannot use indexes to eliminate records that cannot possibly match.

FULLTEXT may be a better solution, but is (a little) more complex to setup

[Performance in using dblink function of postgressql]

What kind of processing do you do with the data from the other database, and how uptodate does that data have to be (I think you know where I'm going with this :-) )

 

DBLink isn't a big risk for security as long as you set the accessrights up correctly, by host/ip and by user.

However, I don't know how DBLink will handle things like indexes if you join tables across a link.

 

I'm curious about why the data lives in separate databases  when you apparently need all the data at the same time in the same database to do this task work.