PHP/MySQL multiple connections

[PsychicTide]

Hey guys, have a quick multiple database connection question I hope will be simple to answer. I've worked with this type of situation several times, but I must be missing something.

I have a newspaper website that has 8 databases (news, obituaries, opinion, etc..) which can hold about 7 counties (flagged by a county column variable). I created the seperate databases instead of creating multiple tables on one database, for mass stoarage. I'm trying to create a search-engine to view all past articles based on form input.

Maybe it will just be easier to look at the code...

Form.php

<form action="archiveMore.php">
        <div style="padding-top:82px;"><font style="color:white;">format: (yyyy-mm-dd)</font></div>
        <p style="color:white;">Date: <input style="margin-left:0;" type="text" name="date" /></p><br />
        <p style="color:white;">Category (Must choose one):</p>
        <select name="category">
            <option value="1">News</option>
            <option value="2">Opinion</option>
            <option value="3">Obituaries</option>
        </select><br /><br />
        <p style="color:white;">County:</p>
        <input type="radio" name="county" value="A"><font style="color:white;">County A</font>
        <input type="radio" name="county" value="B"><font style="color:white;">County B</font>
        <input type="radio" name="county" value="C"><font style="color:white;">County C</font>
        <input type="radio" name="county" value="D"><font style="color:white;">County D</font><br />
        <input type="radio" name="county" value="E"><font style="color:white;">County E</font>
        <input type="radio" name="county" value="F"><font style="color:white;">County F</font>
        <input type="radio" name="county" value="G"><font style="color:white;">County G</font><br />
        <input style="margin-top:12px;" type="submit" value="submit" />
    </form>

archiveMore.php

$dt = strip_tags($_GET['date']);
$cat = strip_tags($_GET['category']);
$cnty = strip_tags($_GET['county']);
if ($cat == '1')
{
    $con1 = mysql_connect("db", "un", "pw", true) or die(mysql_error());
    mysql_select_db("db", $con1) or die(mysql_error());
    if (!empty($dt))
    {
        $result = mysql_query("SELECT * FROM news WHERE cc='$cnty' AND date LIKE '%{$dt}%' ORDER BY id DESC ");
    }
    else
    {
        mysql_query("SELECT * FROM news WHERE cc='$cnty' ORDER BY id DESC ");
    }
}
else if ($cat == '2')
{
    $con1 = mysql_connect("db", "un", "pw", true) or die(mysql_error());
    mysql_select_db("db", $con1) or die(mysql_error());
    if (!empty($dt))
    {
        $result = mysql_query("SELECT * FROM opinion WHERE cc='$cnty' AND date LIKE '%{$dt}%' ORDER BY id DESC ");
    }
    else
    {
        mysql_query("SELECT * FROM opinion WHERE cc='$cnty' ORDER BY id DESC ");
    }
}
else if ($cat == '3')
{
    $con1 = mysql_connect("db", "un", "pw", true) or die(mysql_error());
    mysql_select_db("db", $con1) or die(mysql_error());
    if (!empty($dt))
    {
        $result = mysql_query("SELECT * FROM obit WHERE cc='$cnty' AND date LIKE '%{$dt}%' ORDER BY id DESC ");
    }
    else
    {
        mysql_query("SELECT * FROM obit WHERE cc='$cnty' ORDER BY id DESC ");
    }
}
<!-- display results by echo-ing into html tags -->

This currently produces no results. Any help or ideas are much appreciated :).

[diafol]

I had a go at multiple DB UNIONs - useful for multiple table search. Seems to work.

$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

$sql = "(SELECT db1.table17.content AS c, db1.table17.title AS t FROM db1.table17 WHERE db1.table17.content LIKE '%$term%')UNION(SELECT db2.table24.obcontent AS c, db2.table24.obcontent AS t FROM db2.table24 WHERE db2.table24.obcontent LIKE '%$term%')";
$r = mysql_query($sql);

$output = "<h2>Matches</h2>\n";

while($d = mysql_fetch_assoc($r)){
    $output .= "<h3>{$d['t']}</h3>\n<div>{$d['c']}</div>";  
}

echo $output;

[kam735]

How do I fix the error 'Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed' in Central?

[diafol]

Not sure I follow you. The mysql 'user' should be the same for all your users? e.g.

$link = mysql_connect('localhost', 'defaultUser', 'aqkcfnqw6232bhj2');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

I'm assuming you don't give all your users a unique db login.

//EDIT

Ok, I think I got you - all your dbs have different connection details. How about creating a new user in each with identical attributes?