[some query assistance for a non-coder, please]

I had a problem with defining the beginning of a branch: 

 

so i did this:

 

I put a Start ['s'] toggle, 1/0, in ASSOCS

 

    ASSOCS
    p_id    a_id   s   p_to_a      a_to_p
    1       2      1   employs     worked for
    1       3      0   knows       knows
    3       4      0   workedwith  worked with
    4       6      0   knows       knows
    5       3      1   brother to  sister-in-law to
    6       7      1   wife of     husband of
    7       3      0   knows       knows
    5       1      0   test        test_

 

dug it out in my query (a.s AS s):

    SELECT a.p_id, a.a_id, 
     CONCAT(p1.fname,' ', p1.lname) as name1,  
     p_to_a,
     CONCAT(p2.fname,' ', p2.lname) as name2, a.s AS s, a.info
            FROM assocs a 
            INNER JOIN persons p1 ON p1.person_id = a.p_id
            INNER JOIN persons p2 ON p2.person_id = a.a_id

stored it and called it in place of the $level switch:

 

    if (!isset($data[$pid])) {
            return;
        }
        if (in_array($pid, $processed)) return; // prevent circular references
        $processed[] = $pid;
        
        if ($data[$pid]['s']==1) {
         echo "<div class=''>{$data[$pid]['name']}</b></div>"; 
    }
    

right, I can't believe it worked, either.

 

You're the best, Barand. You've helped me a lot. Now I'm ready to take this thing on the road...

[adding a field to an array]

SOLUTION:

Instead of adding the field to PERSONS, I added put it in ASSOCS:

p_id    a_id     p_to_a     a_to_p         info
1        2      employs   worked for     "info..."
1        3       knows      knows         (null)
3        4    worked with worked with     (null)

 

SELECT a.p_id, a.a_id, 

CONCAT(p1.fname,' ', p1.lname) as name1,  

p_to_a,

CONCAT(p2.fname,' ', p2.lname) as name2, a.info

        FROM assocs a 

        INNER JOIN persons p1 ON p1.person_id = a.p_id

        INNER JOIN persons p2 ON p2.person_id = a.a_id

 

 

 

the output ($nf, 'info' => $nf, $data[$pid]['info'], $adata['info']):

$data = [];
$res = $db->query($sql);
while (list($pid, $aid, $n1, $ass, $n2, $nf) = $res->fetch_row()) {
    if (!isset($data[$pid])) {
        $data[$pid] =  [ 'name' => $n1, 'assocs' => [], 'info' => $nf ];
    }
    $data[$pid]['assocs'][$aid] = ['name' => $n2, 'rel' => $ass, 'info' => $nf];
}


$processed=[];


function listAssociates($pid, &$data, &$processed, $level)
{
    if (!isset($data[$pid])) {
        return;
    }
    if (in_array($pid, $processed)) return; // prevent circular references
    $processed[] = $pid;
    
    if ($level==0) {
     if ($data[$pid]['info'] != '') {
     echo "<div class=''>{$data[$pid]['name']}</b></div><div style='margin-left:0px;'>{$data[$pid]['info']}</div>";
     } else {
echo "<div class=''>{$data[$pid]['name']}</b> » </div>";
}
}
        // $indent = str_repeat(' ', $level*10);
        $indent = ($level*25);
        $indent = ($indent+25)."px";
        foreach ($data[$pid]['assocs'] as $aid=>$adata) {
            echo "<div style='margin-left:$indent'>{$adata['info']}</div><div style='margin-left:$indent'>{$adata['rel']} <b>{$adata['name']}</b></div>\n";
            listAssociates($aid, $data, $processed, $level+1);
        }
}

[some query assistance for a non-coder, please]

ah - I think I've got it - 

 

i can just call the function as needed?
 

listAssociates(1, $data, $processed, 0);
listAssociates(6, $data, $processed, 0);

[some query assistance for a non-coder, please]

Thanks so much, and I apologize for being a pest - the one issue I'm having with this is how to denote where a new branch starts over at level '0' within the loop (see my image in the original question). I certainly don't think it's necessary to run a new query for each branch...?  

 

 

 

it is passed as a parameter to the function

$processed=[];
listAssociates(1, $data, $processed, 0);
                                     |
                                     |
                                     +---- 'level' value

then incremented on subsequent calls

        foreach ($data[$pid]['assocs'] as $aid=>$adata) {
            echo "$indent{$adata['rel']} <b>{$adata['name']}</b><br>\n";
            listAssociates($aid, $data, $processed, $level+1);             <---- CALL AGAIN
        }

[relational tables help]

anybody...?

[relational tables help]

thanks, Andy -

 

yep, i wasn't very clear.

 

what i'm looking for is a proper database schema for data like this. i'll worry about the queries once i have a set of relational tables...

 

i'm going to attach an example of the overall database i have that i need to normalize...

 

i'm trying to get help building a set of tables like this properly.

 

thanks much

gn

 

[attachment deleted by admin]

[relational tables help]

ok, i'll ask it this way:

 

i have an inventory of about 700 products, each with an individual part_number, of course, and a list of only, say, three manufacturers: "SKB", "UK" and "ZERO"

 

i'm attempting to learn good normalization technique and i can't decide how to create a manufacturers table to be relational to my part_numbers table.

 

can someone show me, and an example of a query that will return the associated mfr from a list of part_numbers?

 

i'm very grateful

 

gn

[help with a db layout]

oh, and the id is autoincremented. these are just excerpts from the table with 800 records. and more than 100 options, not just these five or six...

[help with a db layout]

i'm very grateful for your help, DavidAM -

 

very basically this is simply a (MUCH LONGER) list of options a potential buyer of an item would have to choose from, i.e.

 

if someone chooses to view part # ZRC0606-0802, then the options that would be rendered AS AVAILABLE would be

Brass Locks 9.95

Inner Lid  N/A

Hinged Lid FREE

Card Holder 21.06

 

if they viewed #3i-0907-04, the only option available would be

Brass Locks 9.95

 

the important thing, and a question that you asked, is that the price for each Option DOES VARY with any given item, so Brass Locks Option for one part number might or might not cost the same as the same for the next part no.

 

here's an example of what's been done with a very UN-normalized database:

 

go here: http://south-pak.com/search/ and search    AL1010-0904  in the first search field, "Stock Case Search By Part Number" - you'll see what i'm talking about. ALL the available options are listed at the bottom -

 

thanks so much for your help - i'm looking forward to learning more of this stage of database design from you...

 

glenn

 

[help with a db layout]

i've attached an image of a sample of a large dp as put together by my client: items, part numbers and a series of available options and their respective prices all in one table.

 

i'm humbly asking if someone could show me how a couple of relational tables would be built with this data so that i'd be able to query and render an item with its respective options - this db contains almost 1000 products with perhaps 100 columns of possible options/prices versus the few that i've shown here. i'm just needing a way to get started, not knowing if i should make ONE other table with all the options or a table for each column/option represented here... and then how to query an item and its options...

 

i REALLY appreciate any help offered.

 

thanks

GN

 

[attachment deleted by admin]

[help getting started with a somewhat advanced DB]

this was more help than you think, in fact. thanks very much...

[help getting started with a somewhat advanced DB]

i have a fairly large and poorly designed database (i should know, i designed it meself). essentially it contains about 1500 records, EACH RECORD of a unique ITEM with about 25 fields, mostly just options for said item.

 

for an example,in table 'products', item '3R1' will have a value 'colorset_1' in column 'colorset' which refers to a colorset of, say, 'Black, Brown, or Green' while another item would have the value 'colorset2', referring to the colorset 'Black, Brown or Orange', and so forth.

 

i'm hoping that someone could kindly show me how a relational table would be setup and a query called to get the correct colorset for the correct item.

 

 

i'd be very much obliged. once i see how this is done, i'm sure i'd learn a lot by following this for all the other similar columns...

 

thanks much

GN

[not sure how to explain - onchange(this.value) trimming values which have spaces]

i appreciate your assistance. i ended up just replacing the spaces with _ in php and changing them back once the value was passed. worked out, and i don't know javascript well enough at all to try to understand why it wasn't passing the entire value as 'str' ...

 

thanks again

[not sure how to explain - onchange(this.value) trimming values which have spaces]

having this:

$make = "<select name='make' id=\"make\" onchange=\"showModels(this.value)\">";
		 $make .= "<option value=\".$row['value'].\">Value</option>";

 

which calls this function:

function showModels(str)
{
if (str=="")
  {
  document.getElementById("models").innerHTML="";
  return;
  } 
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("models").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","models.php?make="+str,true);
xmlhttp.send();

 

if the value being returned has spaces, like "Convertible 2Door", it's only passing the value "Convertible" (causing some pretty poor queries, ya know)...

 

can anyone see why? any idea what i'm talking about?

 

 

[detecting "index.php" versus "index.php?var=xxx"]

brilliant, worked, thanks.

 

and yes, Ronnie indubitably Wood. no question about it.

[detecting "index.php" versus "index.php?var=xxx"]

i'm running code on what i only want to be the HOME page, but the current site it built using GET variables with index.php

 

needless to say, the home page is accessed either as domain.com/ or domain.com/index.php - i'm hoping someone can show me how to check for this condition, that it is only index.php without anything further

 

I'm only slightly familiar with $_SERVER['uri'], etc... So i don't know just what to ask.

 

thanks for anyone's help.

 

Glenn

 

[help with posting to XML]

i'm doing a form that posts values to XML, and i don't know how.

 

can someone help me with a quick lesson in passing variables/XML data? i don't even know what question to ask, actually -

 

the XML format i've been given is:

<?xml version="1.0" encoding="UTF-8"?>
<lead>
    <innerNode></innerNode>
    <last_name>Smith</last_name>
    <first_name>John</first_name>
</lead>

 

and what i've come up with so far is this, but i don't even know if i'm on the right track:

 

$last_name = $_REQUEST['l_name'];
$first_name = $_REQUEST['f_name'];


$url = "http://url.com/leads";

$post_string = '<?xml version="1.0" encoding="UTF-8"?>
<lead>
    <innerNode></innerNode>
    <last_name>'.$last_name.'</last_name>
    <first_name>'.$first_name.'</first_name>
</lead>';


$header  = "POST HTTP/1.0 \r\n";
$header .= "Content-type: text/xml \r\n";
$header .= "Content-length: ".strlen($post_string)." \r\n";
$header .= "Content-transfer-encoding: text \r\n";
$header .= "Connection: close \r\n\r\n"; 
$header = $post_string;

$ch = curl_init();
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0); 
curl_setopt($ch, CURLOPT_URL,$url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_TIMEOUT, 4);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, $header);

$data = curl_exec($ch); 

if(curl_errno($ch)) {
    print curl_error($ch);
} else {
echo "yay";
    curl_close($ch);
}

 

i'm being told "should return a response.xml with either a success or failure post status." which is confusing me

 

can someone help a bit?

 

thanks very much...

 

GN