Jump to content

OldWest

Members
 View Profile  See their activity

  • Posts

    296

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by OldWest

    New set of eyes on my Query - UPDATE, SET 2 Tables

    in MySQL Help

    Posted

    Can anyone just have a look at tell me if my query and tell me if it seems optimal and optimized for what I am trying to do.

     

    I've got 2 tables:

     

    locations_all

    sys_states

     

    I need to update a blank field locations_all.state_id to sys_states.id where locations_all.city_state equals sys_states.stateName

     

    Here is my current query:

     

    UPDATE locations_all AS a JOIN sys_states AS b ON a.city_state = b.stateName SET a.state_id = b.id

     

    Does it seem efficient this way?

     

    Cannot get SET to work properly w/ Array values

    in PHP Coding Help

    Posted

    I forgot to include my db connection in my last try

     

    In case you haven't seen it posted in the forum, you should be learning php, developing php code, and debugging php code on a system with error_reporting set to E_ALL and display_errors set to ON (set in your master php.ini when ever possible) so that all the php errors will be reported and displayed. You will save a ton of time. There would have been errors alerting you that no connection existed.

     

    I have it set that way (actual read an earlier reply from you to another I believe), and I realized after my last post I was loading the wrong file in my local host! So it was dual errors on my end that lead to total confusion! Thanks for reinforcing good practice!

    Cannot get SET to work properly w/ Array values

    in PHP Coding Help

    Posted

    Oh, and I just noticed. What is the while loop there for?

     

    Thanks for the tips on this.. I'm at the point in my php knowledge where I'm just about very dangerous :) .. Don't know why I am doing what I am doing all of the time!

     

    I updated the SET to include the ' 's around the variable names. That did not seem to change anything. I'm going to keep hacking on it until I get a new error  :confused:

    Cannot get SET to work properly w/ Array values

    in PHP Coding Help

    Posted

    I cannot get my script to update table: locations_all column city_state to the full state name.. Here's what I've been hacking at for 4.5 hours now! Ready to hit the sack.. Thanks for any input:

     

    $state_list = array('AL' => "Alabama", 'AK' => "Alaska", 'AZ' => "Arizona", 'AR' => "Arkansas", 'CA' => "California", 'CO' => "Colorado", 'CT' => "Connecticut", 'DE' => "Delaware", 'DC' => "District Of Columbia", 'FL' => "Florida", 'GA' => "Georgia", 'HI' => "Hawaii", 'ID' => "Idaho", 'IL' => "Illinois", 'IN' => "Indiana", 'IA' => "Iowa", 'KS' => "Kansas", 'KY' => "Kentucky", 'LA' => "Louisiana", 'ME' => "Maine", 'MD' => "Maryland", 'MA' => "Massachusetts", 'MI' => "Michigan", 'MN' => "Minnesota", 'MS' => "Mississippi", 'MO' => "Missouri", 'MT' => "Montana", 'NE' => "Nebraska", 'NV' => "Nevada", 'NH' => "New Hampshire", 'NJ' => "New Jersey", 'NM' => "New Mexico", 'NY' => "New York", 'NC' => "North Carolina", 'ND' => "North Dakota", 'OH' => "Ohio", 'OK' => "Oklahoma", 'OR' => "Oregon", 'PA' => "Pennsylvania", 'RI' => "Rhode Island", 'SC' => "South Carolina", 'SD' => "South Dakota", 'TN' => "Tennessee", 'TX' => "Texas", 'UT' => "Utah", 'VT' => "Vermont", 'VA' => "Virginia", 'WA' => "Washington", 'WV' => "West Virginia", 'WI' => "Wisconsin", 'WY' => "Wyoming");
      foreach ($state_list as $key => $value) {
          // get all state abbr and name values.
          while ($row = mysqli_fetch_array($result)) {
              $query2 = "UPDATE locations_all SET city_state=$value WHERE city_state=$key";
              $result2 = mysqli_query($cxn, $query2) or die("MySQL error: " . mysqli_error($cxn) . "<hr>\nQuery: $query2");
          } //while ($row = mysqli_fetch_array($result))
      } //foreach ($state_list as $key => $value)

     

    To clarify, I am simply trying to swap all state abbreviations with the state full names e.g: AL would be SET to Alabama in the city_state field of locations_all table..

    Cannot get SET to work properly w/ Array values

    in PHP Coding Help

    Posted

    I cannot get my script to update table: locations_all column city_state to the full state name.. Here's what I've been hacking at for 4.5 hours now! Ready to hit the sack.. Thanks for any input:

     

    $state_list = array('AL' => "Alabama", 'AK' => "Alaska", 'AZ' => "Arizona", 'AR' => "Arkansas", 'CA' => "California", 'CO' => "Colorado", 'CT' => "Connecticut", 'DE' => "Delaware", 'DC' => "District Of Columbia", 'FL' => "Florida", 'GA' => "Georgia", 'HI' => "Hawaii", 'ID' => "Idaho", 'IL' => "Illinois", 'IN' => "Indiana", 'IA' => "Iowa", 'KS' => "Kansas", 'KY' => "Kentucky", 'LA' => "Louisiana", 'ME' => "Maine", 'MD' => "Maryland", 'MA' => "Massachusetts", 'MI' => "Michigan", 'MN' => "Minnesota", 'MS' => "Mississippi", 'MO' => "Missouri", 'MT' => "Montana", 'NE' => "Nebraska", 'NV' => "Nevada", 'NH' => "New Hampshire", 'NJ' => "New Jersey", 'NM' => "New Mexico", 'NY' => "New York", 'NC' => "North Carolina", 'ND' => "North Dakota", 'OH' => "Ohio", 'OK' => "Oklahoma", 'OR' => "Oregon", 'PA' => "Pennsylvania", 'RI' => "Rhode Island", 'SC' => "South Carolina", 'SD' => "South Dakota", 'TN' => "Tennessee", 'TX' => "Texas", 'UT' => "Utah", 'VT' => "Vermont", 'VA' => "Virginia", 'WA' => "Washington", 'WV' => "West Virginia", 'WI' => "Wisconsin", 'WY' => "Wyoming");
      foreach ($state_list as $key => $value) {
          // get all state abbr and name values.
          while ($row = mysqli_fetch_array($result)) {
              $query2 = "UPDATE locations_all SET city_state=$value WHERE city_state=$key";
              $result2 = mysqli_query($cxn, $query2) or die("MySQL error: " . mysqli_error($cxn) . "<hr>\nQuery: $query2");
          } //while ($row = mysqli_fetch_array($result))
      } //foreach ($state_list as $key => $value)

     

    Insert into table based on matching values in 2 of 3 tables.

    in PHP Coding Help

    Posted

    I have 3 tables w/ the following fields:

     

    Cities

    id | state_id | city_name

     

    States

    id | state_name

     

    New_cities

    id | state_id | city_name

     

    I am trying to write a script (and still working on it) that will INSERT new cities in cities WHERE New_cities.state_id = Cities.state_id.

     

    Obviously I am still in testing and results sort out, but honest to all hell, I might be going about this all wrong, and would appreciate any comments, criticism, assistance or feedback on what I am trying to do.

     

    <?php
  $query = "SELECT * FROM cities, states, new_cities";
  if ($results = mysqli_query($cxn, $query)) {
      $row_cnt = mysqli_num_rows($results);
      echo $row_cnt . " Total Records in Query.<br /><br />";
      if (mysqli_num_rows($results)) {
          while ($row = mysqli_fetch_array($results)) {
              if ($row['cities']['state_id'] == $row['new_cities']['new_state_id']) {
                  $insert_city_query = "INSERT INTO cities id, new_state_id, city_name VALUES
('','$new_state_id','$city_name')" or mysqli_error();
              } //if ($row['cities']['state_id'] == $row['new_cities']['new_state_id'])
              echo "$row[new_city_name]<br />";
          } //while ($row = mysqli_fetch_array($results))
      } //if (mysqli_num_rows($results))
  } //if ($results = mysqli_query($cxn, $query))
?>

    ElseIf (nesting/syntax) Script Error. A new set of eyes please.

    in PHP Coding Help

    Posted

    I've been hacking at this for 3 hours now.. My syntax seems to be screwed up on something, but I cannot find it for the life of me!

     

    Error thrown is obvious I am missing or have something nested wrong!

    Parse error: parse error in C:\wamp\www\php\php_study\login_processor.php on line 22

     

     

    Here is my code. Error is being thrown on the line of the elseif statment.

     

    <?php
  include('db_cxn.php');
  $cxn = mysqli_connect($host, $user, $pass, $db) or die("Query died: connect");
  $sql = "SELECT username FROM users WHERE username = '$_POST[username]'";
  $result = mysqli_query($cxn, $sql) or die("Query Died: username");
  $num = mysqli_num_rows($result);
  if ($num > 0) {
      $sql = "SELECT username FROM users WHERE username='$_POST[username]' AND password=md5('$_POST[password]')";
      $result2 = mysqli_query($cxn, $sql) or die("Query died: password");
      $num2 = mysqli_num_rows($result2);
      if ($num2 > 0) {
          $_SESSION['auth'];
          $_SESSION['logname'] = $_SESSION['username'];
      } //if ($num2 > 0)
      
      header("Location: the_secret_page.php");
  } //if ($num > 0)
  
  else {
      $message1 = "The login name, '$_POST[username]' does not exist in the database. Please try again";
      $username = strip_tags(trim($_POST[username]));
      include('login_form.php');
  } //else
  
  elseif ($num == 0) {
      $message1 = "The username you entered does not exist. Please try again.s";
      include("login_form.php");
  } //elseif ($num == 0)
?>

     

    I don't think my form html/php code is necessary, but I'll include it for reference:

     

    <h1>Login Form</h1>

<?php
  $fields_1 = array("username" => "User Name", "password" => "Password");
?>

<form method="post" action="login_processor.php">
<fieldset>
<legend>Login Form</legend>
<?php
  if (isset($message_1)) {
      echo "$message_1";
  } //if (isset($message_1))
  foreach ($fields_1 as $field => $value) {
      if (preg_match("/pass/i", $field))
          $type = "password";
      else
          $type = "text";
      echo "<div id='field'>
<label for='$field'>$value</label>
<input id='$field' name='$field' type='$type' value='" . @$$field . "' size='20' maxlength='50' />
</div>\n";
  } //foreach ($fields_1 as $field => $value)
?>
<input type="submit" name="Button" value="Login" />
</fieldset>
</form>

     

     

     

     

    Dynamic Google Site Map Generation (done), but Question Related to my Script

    in PHP Coding Help

    Posted

    I wrote this basic script yesterday to process and generate a Google Site Map. And it works! BUT I want to advance this script to accommodate for something else and I don't know the correct path to take from here, but I will tell you what I've found out so far..

     

    Current Situation:

     

    1 - Currently my below script generates urls in the site map like:

    http://abcdefg.com/index.php?dispatch=products.view&product_id=29826

     

    2 - I have .htaccess configured to rewrite the urls to the products name data like:

    http://abcdefg.com/pennies/wheat-pennies/lincoln-wheat-penny-cent.html (just an example) and these urls are ONLY active if clicking on the site links themselves - meaning if I enter: http://abcdefg.com/index.php?dispatch=products.view&product_id=29826 directly into the url, the url does not resolve to this natural friendly url name.

     

    What Id like to achieve (which I don't know what direction I should be looking!):

     

    - I'd like my xml output urls (as current) to be written in the natural format (as in #2 above).

     

    FYI here is a current example output item in my sitemap:

     

    <url> 
  <loc>http://abcdefg.com/index.php?dispatch=products.view&product_id=29803</loc> 
  <changefreq>weekly</changefreq> 
  <lastmod>2010-09-24T08:00:00+04:00</lastmod> 
</url>

     

    Can anyone give me some guidance on what method might work for this? Do you think it's more a mod_rewrite issue? Or can this be handled easier with straight up modifications to my below? I'm just a bit confused on what direction I should be looking.. Thanks for any input.

     

    <?php
header("Content-Type: text/xml;charset=iso-8859-1");
  echo '<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
//include('config.local.php');
  $cxn = mysqli_connect($config['db_host'], $config['db_user'], $config['db_password'], $config['db_name']);
  $query = "SELECT cscart_product_descriptions.product_id, cscart_products.product_id, cscart_products.timestamp
FROM cscart_product_descriptions JOIN cscart_products ON cscart_product_descriptions.product_id = cscart_products.product_id
WHERE cscart_products.status='A' LIMIT 10000";
  $result = mysqli_query($cxn, $query);
  $row = mysqli_fetch_array($result);
  while ($row = mysqli_fetch_array($result)) {
  $formatedTime = $row['timestamp'];
      echo '<url>
  <loc>http://abcdefg.com/index.php?dispatch=products.view&product_id=' . $row['product_id'] . '</loc>
  <changefreq>weekly</changefreq>
  <lastmod>'. date('c',$formatedTime) .'</lastmod>
  </url>';
  } //while ($row = mysqli_fetch_array($result))
  echo '</urlset>';
?>

     

     

    Some array help

    in PHP Coding Help

    Posted

    Well I managed to get all the information for birth into an array, and users into another array.

    How do I search two arrays and see if there are any matches between them?

     

    It would probably a lot easier just to run a comparison query directly on your tables.

    JOIN Query for 2 Tables - Cannot get a valid result w/out Error

    in MySQL Help

    Posted

    I've got two tables.

     

    I am trying to JOIN both tables by product_id (both tables contain this field), and WHERE status equals A in table1.

     

    Here is what I have so far with absolutely no luck!

     

    $query = "SELECT product_id FROM products, product_descriptions WHERE products.status='A'";

     

    From what I understand, I am  SELECTing product_id from both tables (I also confirmed this much of the statement is working). FROM 2 tables as noted: products, product_descriptions. WHERE products status field equals value A...

     

    Obviously my query is screwed. I've spent some hours on dev.mysql.com and have not been able to piece this together properly..

     

    Thanks for any advice.

     

    Does anyone know why this is not working as expected?

    Echoing Special Characters

    in PHP Coding Help

    Posted

    Replace this line:

     

    $statusTwo=mysql_real_escape_string($_POST['StatusTwo']);

     

    With this:

     

    $statusTwo = $_POST['StatusTwo'];

     

    Probably not such a good idea for security reasons and database vulnerability, but it might work as a temp fix.. Give it a shot!

    why does this not work?

    in PHP Coding Help

    Posted

    That might have been caused by the fact you were also calling:

     

          public function getType() {
         return $this->type;
      }

     

    Two times in the same class. So if you delete one of them, it will probably work with your original naming conventions.

     

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.