PHP - Can The Or Keyword Be Used For Same Field In A Mysql Table?

Problem: I'm trying to build a junction table for a many to many relationship. Table A contains a title string that contains in it names that are stored in Table B. Both tables have nearly a million rows.

Bad solution: The method I'm using right now is using PHP I fetch and store one table into an associative array. I then do a mysql_fetch loop on the second table and run this code to populate an array that is used to build the multi-insert SQL.

$title = preg_replace ($names, $keys, $title);
if (preg_match_all ('/(\d+)/', $title, $matches))
                   foreach ($matches[1] as $m)
                      $inserts[$row['id'].'-'.$m] = 1;

This works, but I'm looking for possibly a less cpu/memory intensive method. My remote server can't handle it, and we're talking hours and hours on a local server. It seems the bottleneck is with the preg_replace. I've also tried str_replace, which is only slightly faster. So, a method where I wouldn't have to pass around such large arrays, or an entirely different approach is what I'm looking for. Any ideas?

Not sure if this topic goes in here, it is related to PHP but also MySql, so if i'm on the wrong board sorry!

What i'm trying to do is search for a keyword in 5 different tables and return the keyword ID from the table that its in

The tables i'm trying to search are as follow

location
state
county
region
continent

The "location" table has all the locations i.e cities and each row has the following columns:
id | continent_id | country_id | state_id | region_id | city_name

The "state" table is set the the following:
id | name

"county" table :
id | name

"region" table:
id | region

and "continent" table
 id | name

The way it works is the can search for any city or state or county or region or continent and ideally it should look into the five different tables and return the id of that table. So if the use searches for United States it will look for United States in all five tables, obviously it would find it in the "country" table so it should return that "id". The results are returned in "json format"

Below is the code i have:

Code: [Select]
<?
$input = $_GET['keyword'];
$data = array();

/*

In this query i'm attempting to search in all databases, but i'm not sure if i'm doing this right.
I'm not getting any results so i know something is wrong just don't know how to write it.

*/

$query = mysql_query("SELECT * FROM locations JOIN states ON states.id = locations.state_id JOIN countries ON countries.id=locations.country_id JOIN regions ON regions.region_id = locations.region_id JOIN continents ON continents.id=locations.continent_id WHERE name LIKE '$input%' OR state LIKE '$input%' OR region LIKE '$input%' OR country LIKE '$input%' OR continent LIKE '$input%'") or die(mysql_error());

/*

Here the values are added to to the $json array. The "value" should be the "id" from the table that the keyword matched. The 'name'
Should be the name of the actual keyword. Again if they search for United States the "id" will come from the "countries" table and the "value"
would come from the "countries" table as the name

*/

while ($row = mysql_fetch_assoc($query)) {
$json = array();
$json['value'] = $row['id'];
$json['name'] = $row['name'];
$data[] = $json;
}

header("Content-type: application/json");

echo json_encode($data);
?>


Any help would he be appreciated, i don't want people to do it for me, but rather just guide me a little bit.

Hi:
I'm going crazy trying to do the following:
I'm making a job registration process where the user registers on one php page to the website, must acknowlege and email receipt using an activate php page, then is directed to upload their C.V. (resume) based on the email address they enter in the active page output. I then run an upload page to store the resume in teh MySQL db based on the users email address in the same record.

If I isolate the process of the user registering to the db, it works perfectly.
If I isolate the file upload process into the db, it works perfect.
I simply cannot upload teh file to the existing record based on teh email form field matching the user_email field in the db.
With the processes together, teh user is activated, but teh file is not uploaded.

Maybe I've simply been at this too long today, but am compeled to get through it by end day.
If anyone can help sugest a better way or help me fix this, I will soo greatly appreciate it.

My code is as follows for the 2 pages.

---------activate.php-------
<?php
session_start();
include ('reg_dbc.php');
if (!isset($_GET['usr']) && !isset($_GET['code']) )
{
$msg = "ERROR: The code does not match..";
exit();
}
$rsCode = mysql_query("SELECT activation_code from subscribers where user_email='$_GET[usr]'") or die(mysql_error());
list($acode) = mysql_fetch_array($rsCode);
if ($_GET['code'] == $acode)
{
mysql_query("update subscribers set user_activated=1 where user_email='$_GET[usr]'") or die(mysql_error());
echo "<h3><center>Thank You! This is step 2 of 3. </h3>Your email is confirmed. Please upload your C.V. now to complete step 3.</center>";
} else
{ echo "ERROR: Incorrect activation code... not valid"; }

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Job application activation</title>
</head>
<body>
<center>
<br/><br/><br/>
<p align="center">
        <form name="form1" method="post" action="upload.php" style="padding:5px;">
        <p>Re-enter you Email : <input name="email" type="text" id="email"/></p></form>
          <form enctype="multipart/form-data" action="upload.php" method="POST">
          <input type="hidden" name="MAX_FILE_SIZE" value="4000000">
          Upload your C.V.: <input name="userfile" type="file" id="userfile">
          <input name="upload" type="submit" id="upload" value="Upload your C.V."/></form>
</p>
</center>
</body>

</html>
--------upload.php----------
<?php
session_start();
if (!isset($_GET['usr']) && !isset($_GET['code']) )
{
$msg = "ERROR: The code does not match..";
exit();
}
if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0)
{
$fileName = $_FILES['userfile']['name'];
$tmpName  = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size'];
$fileType = $_FILES['userfile']['type'];
$email = $_POST['email']['user_email'];

$fp      = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);

if(!get_magic_quotes_gpc())
{
    $fileName = addslashes($fileName);
}

include 'reg_dbc.php';

$query = "UPDATE subscribers WHERE $email = user_email (name, size, type, content ) ". "VALUES ('$fileName', '$fileSize', '$fileType', '$content')";

mysql_query($query) or die('Error, query failed');
mysql_close($dbname);
}
?>
<center>
<br/>
<br/>
<br/>
<br/>
Thank you for uploading your <?php echo "$fileName"; ?> file, completing your registration, and providing us your C.V. for this position.
<br/>
<br/>
<br/>
We will contact you if your canditature qualifies.
</center>

Hi,
I'm developing an app using flash AS2 as the front end via PHP and a mySQL database at the backend on my server.

what i'm looking to do is update/insert into a table called 'cards' and at the same time update/insert into another table called 'jingle'. There is a field called 'cardID' in jingle that should be the same as the ID number created in 'cards' thus creating a link between entries in the different tables that can be called up as i choose.

hope i've been clear i just wouldn't know where to start any help would be appreciated.

MySQL client version: 5.0.91
PHPmyAdmin Version information: 3.2.4


thanks in advance

Hi I am very new to PHP & Mysql. I am trying to insert values into two tables at the same time. One table will insert a single row and the other table will insert multiple records based on user insertion. Everything is working well, but in my second table, 1st Table ID simply insert one time and rest of the values are inserting from 2nd table itself. Now I want to insert the first table's ID Field value (auto-incrementing) to a specific column in the second table (only all last inserted rows).   Ripon.   Below is my Code:

<?php



$con = mysql_connect("localhost","root","aaa");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


mysql_select_db("ccc", $con);


$PI_No = $_POST['PI_No'];
$PO_No = $_POST['PO_No'];


$qry = "INSERT INTO wm_order_entry (
Order_No,
PI_No,
PO_No) 


VALUES(
NULL,
  '$PI_No',
 '$PO_No')";
$result = @mysql_query($qry);




$val1=$_POST['Size'];
$val2=$_POST['Style'];
$val3=$_POST['Colour'];
$val4=$_POST['Season_Code'];
$val5=$_POST['Dept'];
$val6=$_POST['Sub_Item'];
$val7=$_POST['Item_Desc'];
$val8=$_POST['UPC'];
$val9=$_POST['Qty'];
$N = count($val1);
for($i=0; $i < $N; $i++)
{


$profile_query = "INSERT INTO order_entry(Size,
Style,
Colour,
Season_Code,
Dept,
Sub_Item,
Item_Desc,
UPC,
Qty,
Order_No
) VALUES( '$val1[$i]','$val2[$i]','$val3[$i]','$val4[$i]','$val5[$i]','$val6[$i]','$val7[$i]','$val8[$i]','$val9[$i]',LAST_INSERT_ID())";
$t_query=mysql_query($profile_query);
}
header("location: WMView.php");
mysql_close($con);
?>

Output is attached.

Hi i have the following code

Code: [Select]
$orderFetch->order="3";
mysql_query("UPDATE `categories` SET order=order-1 WHERE `order` > $orderFetch->order");

i wish to minus -1 from all the "order" fields in categories that are over a certain value i.e 3. But however this code is not working can anyone tell me where i'm going wrong?

thank you

I have a mysql field that I want to store a php variable in and then retrieve it.   Example: Color table has the field  named colorBackground with the value of #FF0000 CSS table has the field named cssBackground with the value of .background { background-color: #444444; }   What I want to do is make Color/colorBackground a variable {{$backgroundcolor = rsColor['colorBackground']   Then I want to change the CSS/cssBackground value to .background { background-color:$backgroundcolor; }   Creating the $backgroundcolor works fine. I'm just not sure how to put the $backgroundcolor in my CSS/cssBackground field.

I have made a simple form where users who have been subscribed and unsubscribe by inserting their email address.

In my database using PHPMyAdmin, my database to store the emails is 'Links', the table is 'email' and the fields are the 'id' and 'emailaddress'.

What I have tried is making a text input field, where the user ill insert his or her email address, to unsubscribe on the website. As a result the user's field for his or her email address will be delete in the database which is saving the emails for all users who have subscribed.

My HTML codes a

Code: [Select]
<p>Subscribe for newsletters:</p>
<img src="images/k-newsletter-icon.png" width="96" height="96" alt="subscri"/>
<form action="index.php" method="post">
<input type="text" size="25" placeholder="Your email address..." name="enter"/>
<input class="submit" type="submit" value="Subscribe" name="subscribe"/>
</form>

My PHP codes a

Code: [Select]
<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$email = $_POST['enter'];
@mysql_connect ('localhost', 'root', '') or die ('Error');
@mysql_select_db ('links') or die ('Error');
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
echo "Not an email";
return false;    
} else {
mysql_query("DELETE FROM email WHERE emailaddress ='$email'");
echo "deleted";
}
}
?>

When I test it,it is not working, as I see the email which was saved, is still in the database! Help!

I have this script running:


<?php
$query = mysql_query("SELECT * FROM jobs WHERE event = 'Yes' ORDER BY title");
while ($row = mysql_fetch_array($query)) {
?>


How do I check if a field is empty and NOT display it...?

For instance it has a 'applied' field, if that is empty I dont want it to display, however I still  need the event = 'Yes' part.

In my mysql database i have a field that records peoples details one part of the details is their county/region they live in. It records the county in a normal varchar field however i need a simple php script that searching the database and finds the most common county so i can then return to the screen where the most popular region for my members.

Thank you for reading.

Lets say I want to get the name of the first field in a table (or the second) and assign it to a variable.
i.e., if the first fieldname in a table is called "record_number", I would want:
$first = "record_number";

Here's what I was able to come up with so far but not sure what to do next:


$sql = "SELECT * FROM $tbl ";
$result = mysql_query($sql) or die("Query failed : " . mysql_error()); 
$line = mysql_fetch_array($result, MYSQL_ASSOC);

Following from my previous post, i have a questionnaire and within each question it has the options to pick from raddio button (strongly agree, agree, disagree, N/A). For each value of each question i want to pass it into the relevant Field in my database but rather than overwriting the current value i want to be added on to it. (For example if the current value is 2, once the new value (1) has added it will make the new value of 3.).

Hi Guys,

Can someone please help me before I kill myself!

I have a table holding details of images to be displayed in a gallery.
The table has columns as 'id', 'image_caption', 'file_path', and 'userid'.

The 'id' column is the unique key, and the 'userid' column is obviously id of the user who uploaded the image.

What I would like to do is select and display one image from each unique userid and then display each image along with the details in my gallery. It doesn't really matter which users image is selected aslong as there is only one for each unique user.

I have been trying a number of ways to do this (DISTINCT / subqueries) but I just cant get anything to work.
Could someone please advise me on how I could get this to work.

Cheers in advance.

Let's say I have a mysql table named 'function' and a field named 'body' with 1 row.
The 'body' field contains 'Echo out the number 1000 with a comma. ".number_format(1000).". Simple Example.'

As you can see there is a function; number_format(); in the database, but is it possible to execute it onto a live webpage and be displayed correctly?

I see some of the fields in mysql show NULL and some are just empty, wonder what is the difference, is empty field still NULL?