Strip Characters

[NXArmada]

I have 2 MySQL Databases and I want to link them together.

What I am trying do is when you brin up a record say record ID in one DB is RMA-4598 and in the other the record ID is 4598. What I want to do is Strip the RAM- off so im left with 4598 and have the 4598 place into a mySQL query string

<? 
mysql_connect("localhost","root","rootpass"); 
mysql_select_db("service"); 

if($_GET["cmd"]=="view" || $_POST["cmd"]=="view")
{
    $id = $_GET["id"];
    $sql = "SELECT * FROM service WHERE id=$id";
    $result = mysql_query($sql);        
    $myrow = mysql_fetch_array($result);
}
?>

The RMA field for the DB code above is

<?php echo $myrow["svc"] ?>

So i want to strip RMA- from the SVC number and use it as the ID for another DB

<? 
mysql_connect("localhost","root","rootpass"); 
mysql_select_db("service"); 

if($_GET["cmd"]=="view" || $_POST["cmd"]=="view")
{
    $id = $_GET["id"];
    $sql = "SELECT * FROM rma WHERE id=$id";
    $result = mysql_query($sql);        
    $myrow = mysql_fetch_array($result);
}
?>

Once i get that down i should be able to load the Data From both DB's and display on screen.

[mwinter]

I have 2 MySQL Databases and I want to link them together.

It really is in your best interest to use the right terms: you have two tables or relations in a single database.

What I am trying do is when you brin up a record say record ID in one DB is RMA-4598 and in the other the record ID is 4598. What I want to do is Strip the RAM- off so im left with 4598 and have the 4598 place into a mySQL query string

Use the built-in SQL SUBSTRING(str [FROM off [FOR len]]) function. In your example,

SELECT SUBSTRING('RMA-4598' FROM 5)

would return '4598'.

To perform a join, you could use something like:

"SELECT * FROM rma WHERE id=SUBSTRING((SELECT svc FROM service WHERE id=$id) FROM 5)"

The subquery finds the tuple containing the matching id value, and returnings the value from the 'svc' attribute. The SUBSTRING function then removes the first four characters ('RMA-') and that value is then used in the condition for the outer query.

    $id = $_GET["id"];
    $sql = "SELECT * FROM service WHERE id=$id"; 

I really hope that you aren't using data sent with the request directly within a SQL query. That's asking for trouble.

Mike

[NXArmada]

This is for an Intranet.

But thanks i well try our what you posted.

[NXArmada]

Thanks mwinter that was exactly what i wanted.

[Twey]

This is for an Intranet.

That's still asking for trouble, unless you happen to trust every single user (and potential user) of that intranet with all the data in that database, and with the power to insert, alter and delete data. That's the sort of privileges the perpetrator of an SQL injection attack can expect from most SQL setups. It is possible to restrict SQL users' permission to just that necessary, but I doubt you've done that; in any case, it's still more power than you really want to entrust to people you don't know.

[NXArmada]

Well the pages that the user see dont have any fields to change it just displaying a record for print you can't change anything.

[Twey]

Doesn't matter, it's still being passed from the browser to the server (using GET, even worse), and so can be arbitrary content.

[NXArmada]

what would you suggest?

[Twey]

Firstly, you need to keep a reference to the connection. You should have done this anyway.

$conn = mysql_connect("localhost","root","rootpass");

You can then encode the query:

$id = mysql_real_escape_string($_GET["id"], $conn);

[NXArmada]

Thanks Twey i well give that a try.